<HTML>
<BODY BGCOLOR="white">
<PRE>
<FONT color="green">001</FONT>    package edu.hawaii.myisern.derby;<a name="line.1"></a>
<FONT color="green">002</FONT>    <a name="line.2"></a>
<FONT color="green">003</FONT>    import java.io.File;<a name="line.3"></a>
<FONT color="green">004</FONT>    import java.math.BigInteger;<a name="line.4"></a>
<FONT color="green">005</FONT>    import java.sql.Connection;<a name="line.5"></a>
<FONT color="green">006</FONT>    import java.sql.Date;<a name="line.6"></a>
<FONT color="green">007</FONT>    import java.sql.DriverManager;<a name="line.7"></a>
<FONT color="green">008</FONT>    import java.sql.PreparedStatement;<a name="line.8"></a>
<FONT color="green">009</FONT>    import java.sql.ResultSet;<a name="line.9"></a>
<FONT color="green">010</FONT>    import java.sql.SQLException;<a name="line.10"></a>
<FONT color="green">011</FONT>    import java.sql.Statement;<a name="line.11"></a>
<FONT color="green">012</FONT>    import java.sql.Types;<a name="line.12"></a>
<FONT color="green">013</FONT>    <a name="line.13"></a>
<FONT color="green">014</FONT>    import java.util.ArrayList;<a name="line.14"></a>
<FONT color="green">015</FONT>    import java.util.HashMap;<a name="line.15"></a>
<FONT color="green">016</FONT>    import java.util.List;<a name="line.16"></a>
<FONT color="green">017</FONT>    import java.util.Map;<a name="line.17"></a>
<FONT color="green">018</FONT>    import java.util.logging.Level;<a name="line.18"></a>
<FONT color="green">019</FONT>    import java.util.logging.Logger;<a name="line.19"></a>
<FONT color="green">020</FONT>    <a name="line.20"></a>
<FONT color="green">021</FONT>    import javax.xml.bind.JAXBContext;<a name="line.21"></a>
<FONT color="green">022</FONT>    import javax.xml.bind.Unmarshaller;<a name="line.22"></a>
<FONT color="green">023</FONT>    <a name="line.23"></a>
<FONT color="green">024</FONT>    import edu.hawaii.myisern.collaborations.jaxb.Collaboration;<a name="line.24"></a>
<FONT color="green">025</FONT>    import edu.hawaii.myisern.collaborations.jaxb.Collaborations;<a name="line.25"></a>
<FONT color="green">026</FONT>    import edu.hawaii.myisern.exceptions.InvalidCollaborationNameException;<a name="line.26"></a>
<FONT color="green">027</FONT>    import edu.hawaii.myisern.exceptions.InvalidOrganizationNameException;<a name="line.27"></a>
<FONT color="green">028</FONT>    import edu.hawaii.myisern.exceptions.InvalidResearcherNameException;<a name="line.28"></a>
<FONT color="green">029</FONT>    import edu.hawaii.myisern.organizations.jaxb.Organization;<a name="line.29"></a>
<FONT color="green">030</FONT>    import edu.hawaii.myisern.organizations.jaxb.Organizations;<a name="line.30"></a>
<FONT color="green">031</FONT>    import edu.hawaii.myisern.researchers.jaxb.Researcher;<a name="line.31"></a>
<FONT color="green">032</FONT>    import edu.hawaii.myisern.researchers.jaxb.Researchers;<a name="line.32"></a>
<FONT color="green">033</FONT>    /**<a name="line.33"></a>
<FONT color="green">034</FONT>     * Derby wrapper class for myIsern.<a name="line.34"></a>
<FONT color="green">035</FONT>     * Inspired by http://db.apache.org/derby/integrate/plugin_help/derby_app.html.<a name="line.35"></a>
<FONT color="green">036</FONT>     * @author John Hauge<a name="line.36"></a>
<FONT color="green">037</FONT>     *<a name="line.37"></a>
<FONT color="green">038</FONT>     */<a name="line.38"></a>
<FONT color="green">039</FONT>    public class DerbyWrapper {<a name="line.39"></a>
<FONT color="green">040</FONT>      /** The JDBC driver. */<a name="line.40"></a>
<FONT color="green">041</FONT>      private String driver = "org.apache.derby.jdbc.EmbeddedDriver";<a name="line.41"></a>
<FONT color="green">042</FONT>      /** The key for putting/retrieving the directory where Derby will create its databases. */<a name="line.42"></a>
<FONT color="green">043</FONT>      private static final String derbySystemKey = "derby.system.home";<a name="line.43"></a>
<FONT color="green">044</FONT>      private String dbName = "myiserndb";<a name="line.44"></a>
<FONT color="green">045</FONT>      private String dbURL = "jdbc:derby:" + dbName + ";create=true;";<a name="line.45"></a>
<FONT color="green">046</FONT>      <a name="line.46"></a>
<FONT color="green">047</FONT>      private static Connection conn = null;<a name="line.47"></a>
<FONT color="green">048</FONT>      private static Statement stmt = null;<a name="line.48"></a>
<FONT color="green">049</FONT>      <a name="line.49"></a>
<FONT color="green">050</FONT>      private static Logger logger = Logger.getLogger("edu.hawaii.myisern.onyx");<a name="line.50"></a>
<FONT color="green">051</FONT>      <a name="line.51"></a>
<FONT color="green">052</FONT>      private String r_fname = "r_fname";<a name="line.52"></a>
<FONT color="green">053</FONT>      private String r_mname = "r_mname";<a name="line.53"></a>
<FONT color="green">054</FONT>      private String r_lname = "r_lname";<a name="line.54"></a>
<FONT color="green">055</FONT>      private String tbl = "FROM tbl_ORGANIZATION ";<a name="line.55"></a>
<FONT color="green">056</FONT>      <a name="line.56"></a>
<FONT color="green">057</FONT>      /**<a name="line.57"></a>
<FONT color="green">058</FONT>       * Constructor.<a name="line.58"></a>
<FONT color="green">059</FONT>       */<a name="line.59"></a>
<FONT color="green">060</FONT>      public DerbyWrapper() {<a name="line.60"></a>
<FONT color="green">061</FONT>        try {<a name="line.61"></a>
<FONT color="green">062</FONT>          // Make sure the directory exists, creating it if not present.<a name="line.62"></a>
<FONT color="green">063</FONT>          File myisernHome = new File(System.getProperty("user.home"), ".myiserndb");<a name="line.63"></a>
<FONT color="green">064</FONT>          File dbHome = new File(myisernHome, "db");<a name="line.64"></a>
<FONT color="green">065</FONT>          dbHome.mkdirs();<a name="line.65"></a>
<FONT color="green">066</FONT>          // Set the system property used by Derby to locate the db files. <a name="line.66"></a>
<FONT color="green">067</FONT>          System.getProperties().put(derbySystemKey, dbHome.getAbsolutePath());<a name="line.67"></a>
<FONT color="green">068</FONT>          // Now load the Derby driver. <a name="line.68"></a>
<FONT color="green">069</FONT>          createConnection(); <a name="line.69"></a>
<FONT color="green">070</FONT>        } <a name="line.70"></a>
<FONT color="green">071</FONT>        catch (Exception e) {<a name="line.71"></a>
<FONT color="green">072</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.72"></a>
<FONT color="green">073</FONT>        }<a name="line.73"></a>
<FONT color="green">074</FONT>        <a name="line.74"></a>
<FONT color="green">075</FONT>      }<a name="line.75"></a>
<FONT color="green">076</FONT>      /**<a name="line.76"></a>
<FONT color="green">077</FONT>       * Overloaded Constructor.<a name="line.77"></a>
<FONT color="green">078</FONT>       * @param dbName Name of database to connect to.<a name="line.78"></a>
<FONT color="green">079</FONT>       */<a name="line.79"></a>
<FONT color="green">080</FONT>      public DerbyWrapper(String dbName) {<a name="line.80"></a>
<FONT color="green">081</FONT>        try {<a name="line.81"></a>
<FONT color="green">082</FONT>          this.dbName = dbName;<a name="line.82"></a>
<FONT color="green">083</FONT>          this.dbURL = "jdbc:derby:" + dbName + ";create=true;";<a name="line.83"></a>
<FONT color="green">084</FONT>          // Make sure the directory exists, creating it if not present.<a name="line.84"></a>
<FONT color="green">085</FONT>          File myisernHome = new File(System.getProperty("user.home"), ".myiserndb");<a name="line.85"></a>
<FONT color="green">086</FONT>          File dbHome = new File(myisernHome, "db");<a name="line.86"></a>
<FONT color="green">087</FONT>          dbHome.mkdirs();<a name="line.87"></a>
<FONT color="green">088</FONT>          // Set the system property used by Derby to locate the db files. <a name="line.88"></a>
<FONT color="green">089</FONT>          System.getProperties().put(derbySystemKey, dbHome.getAbsolutePath());<a name="line.89"></a>
<FONT color="green">090</FONT>          // Now load the Derby driver. <a name="line.90"></a>
<FONT color="green">091</FONT>          createConnection(); <a name="line.91"></a>
<FONT color="green">092</FONT>        } <a name="line.92"></a>
<FONT color="green">093</FONT>        catch (Exception e) {<a name="line.93"></a>
<FONT color="green">094</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.94"></a>
<FONT color="green">095</FONT>        }<a name="line.95"></a>
<FONT color="green">096</FONT>        <a name="line.96"></a>
<FONT color="green">097</FONT>      }<a name="line.97"></a>
<FONT color="green">098</FONT>      /**<a name="line.98"></a>
<FONT color="green">099</FONT>       * Cleanup.<a name="line.99"></a>
<FONT color="green">100</FONT>       */<a name="line.100"></a>
<FONT color="green">101</FONT>      public void dispose() {<a name="line.101"></a>
<FONT color="green">102</FONT>        closeConnection();<a name="line.102"></a>
<FONT color="green">103</FONT>      }<a name="line.103"></a>
<FONT color="green">104</FONT>      /**<a name="line.104"></a>
<FONT color="green">105</FONT>       * Creates the connection to the database which allows<a name="line.105"></a>
<FONT color="green">106</FONT>       * SQL statements to be executed.<a name="line.106"></a>
<FONT color="green">107</FONT>       */<a name="line.107"></a>
<FONT color="green">108</FONT>      private void createConnection() {<a name="line.108"></a>
<FONT color="green">109</FONT>        try {<a name="line.109"></a>
<FONT color="green">110</FONT>          Class.forName(driver).newInstance();<a name="line.110"></a>
<FONT color="green">111</FONT>          //Get a connection<a name="line.111"></a>
<FONT color="green">112</FONT>          conn = DriverManager.getConnection(dbURL); <a name="line.112"></a>
<FONT color="green">113</FONT>        }<a name="line.113"></a>
<FONT color="green">114</FONT>        catch (Exception e) {<a name="line.114"></a>
<FONT color="green">115</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.115"></a>
<FONT color="green">116</FONT>        }<a name="line.116"></a>
<FONT color="green">117</FONT>      }<a name="line.117"></a>
<FONT color="green">118</FONT>      /**<a name="line.118"></a>
<FONT color="green">119</FONT>       * Closes the connection to the database.<a name="line.119"></a>
<FONT color="green">120</FONT>       */<a name="line.120"></a>
<FONT color="green">121</FONT>      private void closeConnection() {<a name="line.121"></a>
<FONT color="green">122</FONT>        try {<a name="line.122"></a>
<FONT color="green">123</FONT>          if (stmt != null) {<a name="line.123"></a>
<FONT color="green">124</FONT>            stmt.close();<a name="line.124"></a>
<FONT color="green">125</FONT>          }<a name="line.125"></a>
<FONT color="green">126</FONT>          if (conn != null)  {<a name="line.126"></a>
<FONT color="green">127</FONT>            //DriverManager.getConnection(dbURL + ";shutdown=true");<a name="line.127"></a>
<FONT color="green">128</FONT>            conn.close();<a name="line.128"></a>
<FONT color="green">129</FONT>            conn = null;<a name="line.129"></a>
<FONT color="green">130</FONT>          }           <a name="line.130"></a>
<FONT color="green">131</FONT>        }<a name="line.131"></a>
<FONT color="green">132</FONT>        catch (Exception e) {<a name="line.132"></a>
<FONT color="green">133</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.133"></a>
<FONT color="green">134</FONT>        }<a name="line.134"></a>
<FONT color="green">135</FONT>      }<a name="line.135"></a>
<FONT color="green">136</FONT>      <a name="line.136"></a>
<FONT color="green">137</FONT>      /**<a name="line.137"></a>
<FONT color="green">138</FONT>       * Method for importing xml files into the database.<a name="line.138"></a>
<FONT color="green">139</FONT>       * @param importType specifies the type of file importing.<a name="line.139"></a>
<FONT color="green">140</FONT>       * @param filePath path to file.<a name="line.140"></a>
<FONT color="green">141</FONT>       * @return a number.<a name="line.141"></a>
<FONT color="green">142</FONT>       */<a name="line.142"></a>
<FONT color="green">143</FONT>      public int xmlImporter(String importType, String filePath) {<a name="line.143"></a>
<FONT color="green">144</FONT>        try {<a name="line.144"></a>
<FONT color="green">145</FONT>           /** Holds the class-wide JAXBContext, which is thread-safe. */<a name="line.145"></a>
<FONT color="green">146</FONT>          JAXBContext jaxbContext;<a name="line.146"></a>
<FONT color="green">147</FONT>          Collaborations unmarshalledCollab = null;<a name="line.147"></a>
<FONT color="green">148</FONT>          Organizations unmarshalledOrg = null;<a name="line.148"></a>
<FONT color="green">149</FONT>          Researchers unmarshalledResearchers = null;<a name="line.149"></a>
<FONT color="green">150</FONT>          String jaxbContentString = "edu.hawaii.myisern." + importType + ".jaxb";<a name="line.150"></a>
<FONT color="green">151</FONT>          jaxbContext = JAXBContext.newInstance(jaxbContentString);<a name="line.151"></a>
<FONT color="green">152</FONT>          File importFile = new File(filePath);<a name="line.152"></a>
<FONT color="green">153</FONT>          Unmarshaller unmarshaller = jaxbContext.createUnmarshaller();<a name="line.153"></a>
<FONT color="green">154</FONT>          if ("collaborations".equals(importType)) {<a name="line.154"></a>
<FONT color="green">155</FONT>            unmarshalledCollab = (Collaborations) unmarshaller.unmarshal(importFile);<a name="line.155"></a>
<FONT color="green">156</FONT>            for (Collaboration collab : unmarshalledCollab.getCollaboration()) {<a name="line.156"></a>
<FONT color="green">157</FONT>              addCollaboration(collab);<a name="line.157"></a>
<FONT color="green">158</FONT>            }<a name="line.158"></a>
<FONT color="green">159</FONT>          }<a name="line.159"></a>
<FONT color="green">160</FONT>          else if ("organizations".equals(importType)) {<a name="line.160"></a>
<FONT color="green">161</FONT>            unmarshalledOrg = (Organizations) unmarshaller.unmarshal(importFile);<a name="line.161"></a>
<FONT color="green">162</FONT>            for (Organization org : unmarshalledOrg.getOrganization()) {<a name="line.162"></a>
<FONT color="green">163</FONT>              addOrganization(org);<a name="line.163"></a>
<FONT color="green">164</FONT>            }<a name="line.164"></a>
<FONT color="green">165</FONT>          }<a name="line.165"></a>
<FONT color="green">166</FONT>          else if ("researchers".equals(importType)) {<a name="line.166"></a>
<FONT color="green">167</FONT>            unmarshalledResearchers = (Researchers) unmarshaller.unmarshal(importFile);<a name="line.167"></a>
<FONT color="green">168</FONT>            for (Researcher researcher : unmarshalledResearchers.getResearcher()) {<a name="line.168"></a>
<FONT color="green">169</FONT>              addResearcher(researcher);<a name="line.169"></a>
<FONT color="green">170</FONT>            }<a name="line.170"></a>
<FONT color="green">171</FONT>          }<a name="line.171"></a>
<FONT color="green">172</FONT>          //additional operations<a name="line.172"></a>
<FONT color="green">173</FONT>          if ("collaborations".equals(importType)) { <a name="line.173"></a>
<FONT color="green">174</FONT>            int i = 0;<a name="line.174"></a>
<FONT color="green">175</FONT>            /*<a name="line.175"></a>
<FONT color="green">176</FONT>            for (Collaboration collab : unmarshalledCollab.getCollaboration()) {<a name="line.176"></a>
<FONT color="green">177</FONT>              for(String orgName : <a name="line.177"></a>
<FONT color="green">178</FONT>                      collab.getCollaboratingOrganizations().getCollaboratingOrganization()) {<a name="line.178"></a>
<FONT color="green">179</FONT>                int c_id = getCollaborationId(collab.getName());<a name="line.179"></a>
<FONT color="green">180</FONT>                int o_id = getOrganizationId(orgName);<a name="line.180"></a>
<FONT color="green">181</FONT>                if (c_id == -1) {<a name="line.181"></a>
<FONT color="green">182</FONT>                  throw new InvalidCollaborationNameException();<a name="line.182"></a>
<FONT color="green">183</FONT>                }<a name="line.183"></a>
<FONT color="green">184</FONT>                else if (o_id == -1){<a name="line.184"></a>
<FONT color="green">185</FONT>                  throw new InvalidOrganizationNameException();<a name="line.185"></a>
<FONT color="green">186</FONT>                }<a name="line.186"></a>
<FONT color="green">187</FONT>                else {<a name="line.187"></a>
<FONT color="green">188</FONT>                  addCollaboratingOrganization(c_id, o_id);<a name="line.188"></a>
<FONT color="green">189</FONT>                }<a name="line.189"></a>
<FONT color="green">190</FONT>              }<a name="line.190"></a>
<FONT color="green">191</FONT>            }<a name="line.191"></a>
<FONT color="green">192</FONT>            */<a name="line.192"></a>
<FONT color="green">193</FONT>          }<a name="line.193"></a>
<FONT color="green">194</FONT>          else if ("researchers".equals(importType)) {<a name="line.194"></a>
<FONT color="green">195</FONT>            for (Researcher researcher : unmarshalledResearchers.getResearcher()) {<a name="line.195"></a>
<FONT color="green">196</FONT>              int r_id = getResearcherId(researcher.getName());<a name="line.196"></a>
<FONT color="green">197</FONT>              int o_id = getOrganizationId(researcher.getOrganization());<a name="line.197"></a>
<FONT color="green">198</FONT>              //System.out.println("o(" + o_id + "): " + researcher.getOrganization());<a name="line.198"></a>
<FONT color="green">199</FONT>              if (r_id == -1) {<a name="line.199"></a>
<FONT color="green">200</FONT>                throw new InvalidCollaborationNameException(researcher.getName());<a name="line.200"></a>
<FONT color="green">201</FONT>              }<a name="line.201"></a>
<FONT color="green">202</FONT>              else if (o_id == -1) {<a name="line.202"></a>
<FONT color="green">203</FONT>                throw new InvalidOrganizationNameException(researcher.getOrganization());<a name="line.203"></a>
<FONT color="green">204</FONT>              }<a name="line.204"></a>
<FONT color="green">205</FONT>              else {<a name="line.205"></a>
<FONT color="green">206</FONT>                logger.log(Level.INFO, "AddingResearcherOrganization(" + r_id + ", " + o_id + ")");<a name="line.206"></a>
<FONT color="green">207</FONT>                addResearcherOrganization(r_id, o_id);<a name="line.207"></a>
<FONT color="green">208</FONT>              }<a name="line.208"></a>
<FONT color="green">209</FONT>            }<a name="line.209"></a>
<FONT color="green">210</FONT>          }<a name="line.210"></a>
<FONT color="green">211</FONT>          else if ("organizations".equals(importType)) {<a name="line.211"></a>
<FONT color="green">212</FONT>            for (Organization org : unmarshalledOrg.getOrganization()) {<a name="line.212"></a>
<FONT color="green">213</FONT>              for (String r_name : org.getAffiliatedResearchers().getAffiliatedResearcher()) {<a name="line.213"></a>
<FONT color="green">214</FONT>                if (r_name.length() &gt; 0) {<a name="line.214"></a>
<FONT color="green">215</FONT>                  int r_id = getResearcherId(r_name);<a name="line.215"></a>
<FONT color="green">216</FONT>                  int o_id = getOrganizationId(org.getName());<a name="line.216"></a>
<FONT color="green">217</FONT>                  if (r_id == -1) {<a name="line.217"></a>
<FONT color="green">218</FONT>                    //System.out.println("invalid r_name: " + r_name + " --" + r_name.length());<a name="line.218"></a>
<FONT color="green">219</FONT>                    throw new InvalidResearcherNameException(r_name);<a name="line.219"></a>
<FONT color="green">220</FONT>                  }<a name="line.220"></a>
<FONT color="green">221</FONT>                  else if (o_id == -1) {<a name="line.221"></a>
<FONT color="green">222</FONT>                    throw new InvalidOrganizationNameException(org.getName());<a name="line.222"></a>
<FONT color="green">223</FONT>                    //System.out.println("invalid o_name: " + org.getName());<a name="line.223"></a>
<FONT color="green">224</FONT>                  }<a name="line.224"></a>
<FONT color="green">225</FONT>                  else {<a name="line.225"></a>
<FONT color="green">226</FONT>                    logger.log(Level.INFO, "AddingResearcherOrganization(" + r_id + ", " + o_id + ")");<a name="line.226"></a>
<FONT color="green">227</FONT>                    addResearcherOrganization(r_id, o_id);<a name="line.227"></a>
<FONT color="green">228</FONT>                  }<a name="line.228"></a>
<FONT color="green">229</FONT>                }<a name="line.229"></a>
<FONT color="green">230</FONT>              }<a name="line.230"></a>
<FONT color="green">231</FONT>            }<a name="line.231"></a>
<FONT color="green">232</FONT>          }<a name="line.232"></a>
<FONT color="green">233</FONT>          <a name="line.233"></a>
<FONT color="green">234</FONT>          <a name="line.234"></a>
<FONT color="green">235</FONT>          return 0;<a name="line.235"></a>
<FONT color="green">236</FONT>        }<a name="line.236"></a>
<FONT color="green">237</FONT>        catch (Exception e) {<a name="line.237"></a>
<FONT color="green">238</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.238"></a>
<FONT color="green">239</FONT>          return -1;<a name="line.239"></a>
<FONT color="green">240</FONT>        }<a name="line.240"></a>
<FONT color="green">241</FONT>      }<a name="line.241"></a>
<FONT color="green">242</FONT>      /**<a name="line.242"></a>
<FONT color="green">243</FONT>       * Method for exporting xml files from the database.<a name="line.243"></a>
<FONT color="green">244</FONT>       * @param exportType specifies the type of file exporting.<a name="line.244"></a>
<FONT color="green">245</FONT>       * @param filePath path to file.<a name="line.245"></a>
<FONT color="green">246</FONT>       * @return a number.<a name="line.246"></a>
<FONT color="green">247</FONT>       */<a name="line.247"></a>
<FONT color="green">248</FONT>      public int xmlExport(String exportType, String filePath) {<a name="line.248"></a>
<FONT color="green">249</FONT>        <a name="line.249"></a>
<FONT color="green">250</FONT>        <a name="line.250"></a>
<FONT color="green">251</FONT>        return 0;<a name="line.251"></a>
<FONT color="green">252</FONT>      }<a name="line.252"></a>
<FONT color="green">253</FONT>      /**<a name="line.253"></a>
<FONT color="green">254</FONT>       * Creates all tables.<a name="line.254"></a>
<FONT color="green">255</FONT>       */<a name="line.255"></a>
<FONT color="green">256</FONT>      public void createAllTables() {<a name="line.256"></a>
<FONT color="green">257</FONT>        try {<a name="line.257"></a>
<FONT color="green">258</FONT>        //Tables<a name="line.258"></a>
<FONT color="green">259</FONT>          //Organization_Type<a name="line.259"></a>
<FONT color="green">260</FONT>          createOrganizationTypeTable();<a name="line.260"></a>
<FONT color="green">261</FONT>          //Country<a name="line.261"></a>
<FONT color="green">262</FONT>          createCountryTable();<a name="line.262"></a>
<FONT color="green">263</FONT>          //Researcher<a name="line.263"></a>
<FONT color="green">264</FONT>          createResearcherTable(); <a name="line.264"></a>
<FONT color="green">265</FONT>          //Organization<a name="line.265"></a>
<FONT color="green">266</FONT>          createOrganizationTable();<a name="line.266"></a>
<FONT color="green">267</FONT>          //Reseracher_Organization<a name="line.267"></a>
<FONT color="green">268</FONT>          createResearcherOrganizationTable();<a name="line.268"></a>
<FONT color="green">269</FONT>          //Research_Keywords<a name="line.269"></a>
<FONT color="green">270</FONT>          createResearchKeywordTable();<a name="line.270"></a>
<FONT color="green">271</FONT>          //Collaboration<a name="line.271"></a>
<FONT color="green">272</FONT>          createCollaborationTable();<a name="line.272"></a>
<FONT color="green">273</FONT>          //Collaborating_Organizations<a name="line.273"></a>
<FONT color="green">274</FONT>          createCollaboratingOrganizationTable();<a name="line.274"></a>
<FONT color="green">275</FONT>          //Outcome_Type<a name="line.275"></a>
<FONT color="green">276</FONT>          createOutcomeTypeTable();<a name="line.276"></a>
<FONT color="green">277</FONT>          //Years<a name="line.277"></a>
<FONT color="green">278</FONT>          createYearTable();<a name="line.278"></a>
<FONT color="green">279</FONT>          //Collaboration_Type<a name="line.279"></a>
<FONT color="green">280</FONT>          createCollaborationTypeTable();<a name="line.280"></a>
<FONT color="green">281</FONT>        }<a name="line.281"></a>
<FONT color="green">282</FONT>        catch (Exception e) {<a name="line.282"></a>
<FONT color="green">283</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.283"></a>
<FONT color="green">284</FONT>        }<a name="line.284"></a>
<FONT color="green">285</FONT>      }<a name="line.285"></a>
<FONT color="green">286</FONT>      /**<a name="line.286"></a>
<FONT color="green">287</FONT>       * Drops all tables.<a name="line.287"></a>
<FONT color="green">288</FONT>       */<a name="line.288"></a>
<FONT color="green">289</FONT>      public void dropAllTables() {<a name="line.289"></a>
<FONT color="green">290</FONT>        try {<a name="line.290"></a>
<FONT color="green">291</FONT>        //Tables<a name="line.291"></a>
<FONT color="green">292</FONT>          //Collaborating_Organizations<a name="line.292"></a>
<FONT color="green">293</FONT>          dropCollaboratingOrganizationTable();<a name="line.293"></a>
<FONT color="green">294</FONT>          //Research_Keywords<a name="line.294"></a>
<FONT color="green">295</FONT>          dropResearchKeywordTable();<a name="line.295"></a>
<FONT color="green">296</FONT>          //Reseracher_Organization<a name="line.296"></a>
<FONT color="green">297</FONT>          dropResearcherOrganizationTable();<a name="line.297"></a>
<FONT color="green">298</FONT>          //Organization<a name="line.298"></a>
<FONT color="green">299</FONT>          dropOrganizationTable();<a name="line.299"></a>
<FONT color="green">300</FONT>          //Organization_Type<a name="line.300"></a>
<FONT color="green">301</FONT>          dropOrganizationTypeTable();<a name="line.301"></a>
<FONT color="green">302</FONT>          //Country<a name="line.302"></a>
<FONT color="green">303</FONT>          dropCountryTable();<a name="line.303"></a>
<FONT color="green">304</FONT>          //Researcher<a name="line.304"></a>
<FONT color="green">305</FONT>          dropResearcherTable(); <a name="line.305"></a>
<FONT color="green">306</FONT>          //Outcome_Type<a name="line.306"></a>
<FONT color="green">307</FONT>          dropOutcomeTypeTable();<a name="line.307"></a>
<FONT color="green">308</FONT>          //Years<a name="line.308"></a>
<FONT color="green">309</FONT>          dropYearTable();<a name="line.309"></a>
<FONT color="green">310</FONT>          //Collaboration_Type<a name="line.310"></a>
<FONT color="green">311</FONT>          dropCollaborationTypeTable();<a name="line.311"></a>
<FONT color="green">312</FONT>          //Collaboration<a name="line.312"></a>
<FONT color="green">313</FONT>          dropCollaborationTable();<a name="line.313"></a>
<FONT color="green">314</FONT>          <a name="line.314"></a>
<FONT color="green">315</FONT>          <a name="line.315"></a>
<FONT color="green">316</FONT>          <a name="line.316"></a>
<FONT color="green">317</FONT>        }<a name="line.317"></a>
<FONT color="green">318</FONT>        catch (Exception e) {<a name="line.318"></a>
<FONT color="green">319</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.319"></a>
<FONT color="green">320</FONT>        }<a name="line.320"></a>
<FONT color="green">321</FONT>      }<a name="line.321"></a>
<FONT color="green">322</FONT>      /**<a name="line.322"></a>
<FONT color="green">323</FONT>       * Creates the tbl_RESEARCHER Table.<a name="line.323"></a>
<FONT color="green">324</FONT>       */<a name="line.324"></a>
<FONT color="green">325</FONT>      public void createResearcherTable() {<a name="line.325"></a>
<FONT color="green">326</FONT>        try {<a name="line.326"></a>
<FONT color="green">327</FONT>          String sqlCmd = "CREATE TABLE tbl_RESEARCHER (" +<a name="line.327"></a>
<FONT color="green">328</FONT>                          "   r_id      INT           GENERATED ALWAYS AS IDENTITY" + <a name="line.328"></a>
<FONT color="green">329</FONT>                          "                           CONSTRAINT r_id_pk PRIMARY KEY," +<a name="line.329"></a>
<FONT color="green">330</FONT>                          "   r_fname   VARCHAR(64)   NOT NULL," +<a name="line.330"></a>
<FONT color="green">331</FONT>                          "   r_mname   VARCHAR(64)   ," +<a name="line.331"></a>
<FONT color="green">332</FONT>                          "   r_lname   VARCHAR(64)   NOT NULL," +<a name="line.332"></a>
<FONT color="green">333</FONT>                          "   r_email   VARCHAR(128)  ," +<a name="line.333"></a>
<FONT color="green">334</FONT>                          "   r_picLink VARCHAR(256)  ," +<a name="line.334"></a>
<FONT color="green">335</FONT>                          "   r_bio     VARCHAR(1024) " +<a name="line.335"></a>
<FONT color="green">336</FONT>                          ")";<a name="line.336"></a>
<FONT color="green">337</FONT>          stmt = conn.createStatement();<a name="line.337"></a>
<FONT color="green">338</FONT>          stmt.execute(sqlCmd);<a name="line.338"></a>
<FONT color="green">339</FONT>          stmt.close();<a name="line.339"></a>
<FONT color="green">340</FONT>        }<a name="line.340"></a>
<FONT color="green">341</FONT>        catch (Exception e)  {<a name="line.341"></a>
<FONT color="green">342</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.342"></a>
<FONT color="green">343</FONT>        }<a name="line.343"></a>
<FONT color="green">344</FONT>      }<a name="line.344"></a>
<FONT color="green">345</FONT>      /**<a name="line.345"></a>
<FONT color="green">346</FONT>       * Drops the tbl_RESEARCHER Table.<a name="line.346"></a>
<FONT color="green">347</FONT>       */<a name="line.347"></a>
<FONT color="green">348</FONT>      public void dropResearcherTable() {<a name="line.348"></a>
<FONT color="green">349</FONT>        try {<a name="line.349"></a>
<FONT color="green">350</FONT>          String sqlCmd = "DROP TABLE tbl_RESEARCHER";<a name="line.350"></a>
<FONT color="green">351</FONT>          stmt = conn.createStatement();<a name="line.351"></a>
<FONT color="green">352</FONT>          stmt.execute(sqlCmd);<a name="line.352"></a>
<FONT color="green">353</FONT>          stmt.close();<a name="line.353"></a>
<FONT color="green">354</FONT>        }<a name="line.354"></a>
<FONT color="green">355</FONT>        catch (Exception e)  {<a name="line.355"></a>
<FONT color="green">356</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.356"></a>
<FONT color="green">357</FONT>        }<a name="line.357"></a>
<FONT color="green">358</FONT>      }<a name="line.358"></a>
<FONT color="green">359</FONT>      <a name="line.359"></a>
<FONT color="green">360</FONT>      /**<a name="line.360"></a>
<FONT color="green">361</FONT>       * Adds a researcher to the table "tbl_RESEARCHER" in the database.<a name="line.361"></a>
<FONT color="green">362</FONT>       * @param r Researcher to add.<a name="line.362"></a>
<FONT color="green">363</FONT>       */<a name="line.363"></a>
<FONT color="green">364</FONT>      public void addResearcher(Researcher r) {<a name="line.364"></a>
<FONT color="green">365</FONT>        try {<a name="line.365"></a>
<FONT color="green">366</FONT>          String[] names = splitUpName(r.getName());<a name="line.366"></a>
<FONT color="green">367</FONT>          String sqlCmd = "INSERT INTO tbl_RESEARCHER " + <a name="line.367"></a>
<FONT color="green">368</FONT>                          "(r_fname, r_mname, r_lname, r_email, r_picLink, r_bio)" +<a name="line.368"></a>
<FONT color="green">369</FONT>                          "VALUES (?,?,?,?,?,?)";<a name="line.369"></a>
<FONT color="green">370</FONT>          PreparedStatement pStmt = conn.prepareStatement(sqlCmd);<a name="line.370"></a>
<FONT color="green">371</FONT>          pStmt.setString(1, names[0]);<a name="line.371"></a>
<FONT color="green">372</FONT>          pStmt.setString(2, names[1]);<a name="line.372"></a>
<FONT color="green">373</FONT>          pStmt.setString(3, names[2]);<a name="line.373"></a>
<FONT color="green">374</FONT>          pStmt.setString(4, r.getEmail());<a name="line.374"></a>
<FONT color="green">375</FONT>          pStmt.setString(5, r.getPictureLink());<a name="line.375"></a>
<FONT color="green">376</FONT>          pStmt.setString(6, r.getBioStatement());<a name="line.376"></a>
<FONT color="green">377</FONT>          pStmt.execute();<a name="line.377"></a>
<FONT color="green">378</FONT>          pStmt.close();<a name="line.378"></a>
<FONT color="green">379</FONT>          <a name="line.379"></a>
<FONT color="green">380</FONT>          sqlCmd = "INSERT INTO tbl_RESEARCHER_ORGANIZATION" + <a name="line.380"></a>
<FONT color="green">381</FONT>                   "(r_id, o_id) VALUES (?,?)";<a name="line.381"></a>
<FONT color="green">382</FONT>          //TODO Support multiple orgs when [Issue 2] taken care of<a name="line.382"></a>
<FONT color="green">383</FONT>          pStmt = conn.prepareStatement(sqlCmd);<a name="line.383"></a>
<FONT color="green">384</FONT>          int id = getResearcherId(r.getName());<a name="line.384"></a>
<FONT color="green">385</FONT>          pStmt.setInt(1, id);<a name="line.385"></a>
<FONT color="green">386</FONT>          id = getOrganizationId(r.getOrganization());<a name="line.386"></a>
<FONT color="green">387</FONT>          if (id == -1) {<a name="line.387"></a>
<FONT color="green">388</FONT>            throw new InvalidOrganizationNameException(r.getOrganization());<a name="line.388"></a>
<FONT color="green">389</FONT>          }<a name="line.389"></a>
<FONT color="green">390</FONT>          else {<a name="line.390"></a>
<FONT color="green">391</FONT>            pStmt.setInt(2, id);<a name="line.391"></a>
<FONT color="green">392</FONT>            pStmt.execute();<a name="line.392"></a>
<FONT color="green">393</FONT>          }<a name="line.393"></a>
<FONT color="green">394</FONT>          pStmt.close();<a name="line.394"></a>
<FONT color="green">395</FONT>          <a name="line.395"></a>
<FONT color="green">396</FONT>          <a name="line.396"></a>
<FONT color="green">397</FONT>        }<a name="line.397"></a>
<FONT color="green">398</FONT>        catch (Exception e)  {<a name="line.398"></a>
<FONT color="green">399</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.399"></a>
<FONT color="green">400</FONT>        }<a name="line.400"></a>
<FONT color="green">401</FONT>      }<a name="line.401"></a>
<FONT color="green">402</FONT>      /**<a name="line.402"></a>
<FONT color="green">403</FONT>       * Get a researcher provided the database ID.<a name="line.403"></a>
<FONT color="green">404</FONT>       * @param id of the researcher.<a name="line.404"></a>
<FONT color="green">405</FONT>       * @return a researcher object.<a name="line.405"></a>
<FONT color="green">406</FONT>       * @throws Exception if error occurs.<a name="line.406"></a>
<FONT color="green">407</FONT>       */<a name="line.407"></a>
<FONT color="green">408</FONT>      public Researcher getResearcher(int id) throws Exception {<a name="line.408"></a>
<FONT color="green">409</FONT>        PreparedStatement pStmt = null;<a name="line.409"></a>
<FONT color="green">410</FONT>        Researcher r = new Researcher();<a name="line.410"></a>
<FONT color="green">411</FONT>        ResultSet results = null;<a name="line.411"></a>
<FONT color="green">412</FONT>        try {<a name="line.412"></a>
<FONT color="green">413</FONT>          String sqlCmd = "SELECT r_fname, r_mname, r_lname, r_email, r_picLink, r_bio " +<a name="line.413"></a>
<FONT color="green">414</FONT>                          "FROM tbl_RESEARCHER WHERE r_id = ?";<a name="line.414"></a>
<FONT color="green">415</FONT>          //System.out.println("sqlCmd: " + sqlCmd);<a name="line.415"></a>
<FONT color="green">416</FONT>          pStmt = conn.prepareStatement(sqlCmd);<a name="line.416"></a>
<FONT color="green">417</FONT>          pStmt.setInt(1, id);<a name="line.417"></a>
<FONT color="green">418</FONT>          results = pStmt.executeQuery();<a name="line.418"></a>
<FONT color="green">419</FONT>          results.next();<a name="line.419"></a>
<FONT color="green">420</FONT>          //System.out.println("(id: " + id + ")\tResults: " + results.toString());<a name="line.420"></a>
<FONT color="green">421</FONT>          //System.out.println("Results fname: " + results.getString(r_fname));<a name="line.421"></a>
<FONT color="green">422</FONT>          <a name="line.422"></a>
<FONT color="green">423</FONT>          r.setName(results.getString(r_fname) + " " + <a name="line.423"></a>
<FONT color="green">424</FONT>                    results.getString(r_mname) + " " +<a name="line.424"></a>
<FONT color="green">425</FONT>                    results.getString(r_lname));<a name="line.425"></a>
<FONT color="green">426</FONT>          r.setEmail(results.getString("r_email"));<a name="line.426"></a>
<FONT color="green">427</FONT>          r.setPictureLink(results.getString("r_picLink"));<a name="line.427"></a>
<FONT color="green">428</FONT>          r.setBioStatement(results.getString("r_bio"));<a name="line.428"></a>
<FONT color="green">429</FONT>          //Still need to add in code for multiple organizations<a name="line.429"></a>
<FONT color="green">430</FONT>          <a name="line.430"></a>
<FONT color="green">431</FONT>        }<a name="line.431"></a>
<FONT color="green">432</FONT>        catch (Exception e)  {<a name="line.432"></a>
<FONT color="green">433</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.433"></a>
<FONT color="green">434</FONT>          r = null;<a name="line.434"></a>
<FONT color="green">435</FONT>        }<a name="line.435"></a>
<FONT color="green">436</FONT>        finally {<a name="line.436"></a>
<FONT color="green">437</FONT>          results.close();<a name="line.437"></a>
<FONT color="green">438</FONT>          pStmt.close();<a name="line.438"></a>
<FONT color="green">439</FONT>        }<a name="line.439"></a>
<FONT color="green">440</FONT>        return r;<a name="line.440"></a>
<FONT color="green">441</FONT>      }<a name="line.441"></a>
<FONT color="green">442</FONT>      /**<a name="line.442"></a>
<FONT color="green">443</FONT>       * Gets the database ID of an researcher given their name<a name="line.443"></a>
<FONT color="green">444</FONT>       * from the "Researcher" table.<a name="line.444"></a>
<FONT color="green">445</FONT>       * @param name of the researcher.<a name="line.445"></a>
<FONT color="green">446</FONT>       * @return the id number of the researcher.<a name="line.446"></a>
<FONT color="green">447</FONT>       * @throws Exception if error occurs.<a name="line.447"></a>
<FONT color="green">448</FONT>       */<a name="line.448"></a>
<FONT color="green">449</FONT>      public int getResearcherId(String name) throws Exception {<a name="line.449"></a>
<FONT color="green">450</FONT>        int id = -1;<a name="line.450"></a>
<FONT color="green">451</FONT>        PreparedStatement pStmt = null;<a name="line.451"></a>
<FONT color="green">452</FONT>        ResultSet results = null;<a name="line.452"></a>
<FONT color="green">453</FONT>        try {<a name="line.453"></a>
<FONT color="green">454</FONT>          //System.out.println("Name: " + name);<a name="line.454"></a>
<FONT color="green">455</FONT>          String[] names = splitUpName(name);<a name="line.455"></a>
<FONT color="green">456</FONT>          /*<a name="line.456"></a>
<FONT color="green">457</FONT>          System.out.println("fname: " + names[0]);<a name="line.457"></a>
<FONT color="green">458</FONT>          System.out.println("mname: " + names[1]);<a name="line.458"></a>
<FONT color="green">459</FONT>          System.out.println("lname: " + names[2]);<a name="line.459"></a>
<FONT color="green">460</FONT>          */<a name="line.460"></a>
<FONT color="green">461</FONT>          String sqlCmd = "SELECT r_id " + <a name="line.461"></a>
<FONT color="green">462</FONT>                          "FROM tbl_RESEARCHER " +  <a name="line.462"></a>
<FONT color="green">463</FONT>                          "WHERE r_fname = ? " + <a name="line.463"></a>
<FONT color="green">464</FONT>                          "AND r_mname = ? " + <a name="line.464"></a>
<FONT color="green">465</FONT>                          "AND r_lname = ? ";<a name="line.465"></a>
<FONT color="green">466</FONT>          pStmt = conn.prepareStatement(sqlCmd);<a name="line.466"></a>
<FONT color="green">467</FONT>          pStmt.setString(1, names[0]);<a name="line.467"></a>
<FONT color="green">468</FONT>          pStmt.setString(2, names[1]);<a name="line.468"></a>
<FONT color="green">469</FONT>          pStmt.setString(3, names[2]);<a name="line.469"></a>
<FONT color="green">470</FONT>          results = pStmt.executeQuery();<a name="line.470"></a>
<FONT color="green">471</FONT>          if (results.next()) {<a name="line.471"></a>
<FONT color="green">472</FONT>            id = results.getInt("r_id");<a name="line.472"></a>
<FONT color="green">473</FONT>          }<a name="line.473"></a>
<FONT color="green">474</FONT>          else {<a name="line.474"></a>
<FONT color="green">475</FONT>            id = -1;<a name="line.475"></a>
<FONT color="green">476</FONT>          }<a name="line.476"></a>
<FONT color="green">477</FONT>        }<a name="line.477"></a>
<FONT color="green">478</FONT>        catch (Exception e) {<a name="line.478"></a>
<FONT color="green">479</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.479"></a>
<FONT color="green">480</FONT>        }<a name="line.480"></a>
<FONT color="green">481</FONT>        finally {<a name="line.481"></a>
<FONT color="green">482</FONT>          results.close();<a name="line.482"></a>
<FONT color="green">483</FONT>          pStmt.close();<a name="line.483"></a>
<FONT color="green">484</FONT>        }<a name="line.484"></a>
<FONT color="green">485</FONT>        return id;<a name="line.485"></a>
<FONT color="green">486</FONT>      }<a name="line.486"></a>
<FONT color="green">487</FONT>      /**<a name="line.487"></a>
<FONT color="green">488</FONT>       * Gets a list of all researchers in the tbl_RESEARCHER table<a name="line.488"></a>
<FONT color="green">489</FONT>       * @return an array list of researcher objects.<a name="line.489"></a>
<FONT color="green">490</FONT>       * @throws Exception if error occurs.<a name="line.490"></a>
<FONT color="green">491</FONT>       */<a name="line.491"></a>
<FONT color="green">492</FONT>      public ArrayList&lt;Researcher&gt; getResearcherList() throws Exception {<a name="line.492"></a>
<FONT color="green">493</FONT>        ArrayList&lt;Researcher&gt; list = new ArrayList&lt;Researcher&gt;();<a name="line.493"></a>
<FONT color="green">494</FONT>        Researcher r;<a name="line.494"></a>
<FONT color="green">495</FONT>        ResultSet results = null;<a name="line.495"></a>
<FONT color="green">496</FONT>        try {<a name="line.496"></a>
<FONT color="green">497</FONT>          String sqlCmd = "SELECT r_fname, r_mname, r_lname, r_email, r_picLink, r_bio " +<a name="line.497"></a>
<FONT color="green">498</FONT>                          "FROM tbl_RESEARCHER ";<a name="line.498"></a>
<FONT color="green">499</FONT>          stmt = conn.createStatement();<a name="line.499"></a>
<FONT color="green">500</FONT>          results = stmt.executeQuery(sqlCmd);<a name="line.500"></a>
<FONT color="green">501</FONT>          while (results.next()) {<a name="line.501"></a>
<FONT color="green">502</FONT>            r = new Researcher();<a name="line.502"></a>
<FONT color="green">503</FONT>           <a name="line.503"></a>
<FONT color="green">504</FONT>            r.setName(results.getString(this.r_fname) + " " + <a name="line.504"></a>
<FONT color="green">505</FONT>                      results.getString(this.r_mname) + " " +<a name="line.505"></a>
<FONT color="green">506</FONT>                      results.getString(this.r_lname));<a name="line.506"></a>
<FONT color="green">507</FONT>            r.setEmail(results.getString("r_email"));<a name="line.507"></a>
<FONT color="green">508</FONT>            r.setPictureLink(results.getString("r_picLink"));<a name="line.508"></a>
<FONT color="green">509</FONT>            r.setBioStatement(results.getString("r_bio"));<a name="line.509"></a>
<FONT color="green">510</FONT>            list.add(r);<a name="line.510"></a>
<FONT color="green">511</FONT>          }<a name="line.511"></a>
<FONT color="green">512</FONT>        }<a name="line.512"></a>
<FONT color="green">513</FONT>        catch (Exception e)  {<a name="line.513"></a>
<FONT color="green">514</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.514"></a>
<FONT color="green">515</FONT>          list = null;<a name="line.515"></a>
<FONT color="green">516</FONT>        }<a name="line.516"></a>
<FONT color="green">517</FONT>        finally {<a name="line.517"></a>
<FONT color="green">518</FONT>          results.close();<a name="line.518"></a>
<FONT color="green">519</FONT>          stmt.close();<a name="line.519"></a>
<FONT color="green">520</FONT>        }<a name="line.520"></a>
<FONT color="green">521</FONT>        return list;<a name="line.521"></a>
<FONT color="green">522</FONT>        <a name="line.522"></a>
<FONT color="green">523</FONT>      }<a name="line.523"></a>
<FONT color="green">524</FONT>      /**<a name="line.524"></a>
<FONT color="green">525</FONT>       * Gets a list of the names of all researchers in the tbl_RESEARCHER <a name="line.525"></a>
<FONT color="green">526</FONT>       * table.<a name="line.526"></a>
<FONT color="green">527</FONT>       * @return List of researcher names.<a name="line.527"></a>
<FONT color="green">528</FONT>       * @throws SQLException If error occurs.<a name="line.528"></a>
<FONT color="green">529</FONT>       */<a name="line.529"></a>
<FONT color="green">530</FONT>      public List&lt;String&gt; getResearcherNameList() throws SQLException {<a name="line.530"></a>
<FONT color="green">531</FONT>        ArrayList&lt;String&gt; list = new ArrayList&lt;String&gt;();<a name="line.531"></a>
<FONT color="green">532</FONT>        PreparedStatement pStmt = null;<a name="line.532"></a>
<FONT color="green">533</FONT>        ResultSet results = null;<a name="line.533"></a>
<FONT color="green">534</FONT>        try {<a name="line.534"></a>
<FONT color="green">535</FONT>          String sqlCmd = "SELECT r_fname, r_mname, r_lname " + <a name="line.535"></a>
<FONT color="green">536</FONT>                          "FROM tbl_RESEARCHER ";  <a name="line.536"></a>
<FONT color="green">537</FONT>          pStmt = conn.prepareStatement(sqlCmd);<a name="line.537"></a>
<FONT color="green">538</FONT>          results = pStmt.executeQuery();<a name="line.538"></a>
<FONT color="green">539</FONT>          while (results.next()) {<a name="line.539"></a>
<FONT color="green">540</FONT>            list.add(getFullName(results.getString(r_fname), <a name="line.540"></a>
<FONT color="green">541</FONT>                                 results.getString(r_mname),<a name="line.541"></a>
<FONT color="green">542</FONT>                                 results.getString(r_lname)));<a name="line.542"></a>
<FONT color="green">543</FONT>          }<a name="line.543"></a>
<FONT color="green">544</FONT>          return list;<a name="line.544"></a>
<FONT color="green">545</FONT>        }<a name="line.545"></a>
<FONT color="green">546</FONT>        catch (Exception e) {<a name="line.546"></a>
<FONT color="green">547</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.547"></a>
<FONT color="green">548</FONT>          return null;<a name="line.548"></a>
<FONT color="green">549</FONT>        }<a name="line.549"></a>
<FONT color="green">550</FONT>        finally {<a name="line.550"></a>
<FONT color="green">551</FONT>          pStmt.close();<a name="line.551"></a>
<FONT color="green">552</FONT>          results.close();<a name="line.552"></a>
<FONT color="green">553</FONT>        }<a name="line.553"></a>
<FONT color="green">554</FONT>      }<a name="line.554"></a>
<FONT color="green">555</FONT>      /**<a name="line.555"></a>
<FONT color="green">556</FONT>       * Creates the tbl_ORGANIZATION Table.<a name="line.556"></a>
<FONT color="green">557</FONT>       */<a name="line.557"></a>
<FONT color="green">558</FONT>      public void createOrganizationTable() {<a name="line.558"></a>
<FONT color="green">559</FONT>        try {<a name="line.559"></a>
<FONT color="green">560</FONT>          String sqlCmd = "CREATE TABLE tbl_ORGANIZATION (" +<a name="line.560"></a>
<FONT color="green">561</FONT>                          "   o_id          INT           GENERATED ALWAYS AS IDENTITY" + <a name="line.561"></a>
<FONT color="green">562</FONT>                          "                               CONSTRAINT o_id_pk PRIMARY KEY," +<a name="line.562"></a>
<FONT color="green">563</FONT>                          "   o_name        VARCHAR(64)   NOT NULL" +<a name="line.563"></a>
<FONT color="green">564</FONT>                          "                               CONSTRAINT o_name_uc UNIQUE," +<a name="line.564"></a>
<FONT color="green">565</FONT>                          "   o_type_id     INT           CONSTRAINT o_type_id_fk" +<a name="line.565"></a>
<FONT color="green">566</FONT>                          "                               REFERENCES tbl_ORGANIZATION_TYPE," +<a name="line.566"></a>
<FONT color="green">567</FONT>                          "   o_contact_id  INT           CONSTRAINT o_contact_id_fk" +<a name="line.567"></a>
<FONT color="green">568</FONT>                          "                               REFERENCES tbl_RESEARCHER," +<a name="line.568"></a>
<FONT color="green">569</FONT>                          "   o_country_id  INT           CONSTRAINT o_country_id_fk" +<a name="line.569"></a>
<FONT color="green">570</FONT>                          "                               REFERENCES tbl_COUNTRY," +<a name="line.570"></a>
<FONT color="green">571</FONT>                          "   o_desc        VARCHAR(1028), " +<a name="line.571"></a>
<FONT color="green">572</FONT>                          "   o_homepage    VARCHAR(256) " +<a name="line.572"></a>
<FONT color="green">573</FONT>                          ")";<a name="line.573"></a>
<FONT color="green">574</FONT>          stmt = conn.createStatement();<a name="line.574"></a>
<FONT color="green">575</FONT>          stmt.execute(sqlCmd);<a name="line.575"></a>
<FONT color="green">576</FONT>          stmt.close();<a name="line.576"></a>
<FONT color="green">577</FONT>        }<a name="line.577"></a>
<FONT color="green">578</FONT>        catch (Exception e)  {<a name="line.578"></a>
<FONT color="green">579</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.579"></a>
<FONT color="green">580</FONT>        }<a name="line.580"></a>
<FONT color="green">581</FONT>      }<a name="line.581"></a>
<FONT color="green">582</FONT>      /**<a name="line.582"></a>
<FONT color="green">583</FONT>       * Drops the tbl_ORGANIZATION Table.<a name="line.583"></a>
<FONT color="green">584</FONT>       */<a name="line.584"></a>
<FONT color="green">585</FONT>      public void dropOrganizationTable() {<a name="line.585"></a>
<FONT color="green">586</FONT>        try {<a name="line.586"></a>
<FONT color="green">587</FONT>          String sqlCmd = " DROP TABLE tbl_ORGANIZATION";<a name="line.587"></a>
<FONT color="green">588</FONT>          stmt = conn.createStatement();<a name="line.588"></a>
<FONT color="green">589</FONT>          stmt.execute(sqlCmd);<a name="line.589"></a>
<FONT color="green">590</FONT>          stmt.close();<a name="line.590"></a>
<FONT color="green">591</FONT>          <a name="line.591"></a>
<FONT color="green">592</FONT>        }<a name="line.592"></a>
<FONT color="green">593</FONT>        catch (Exception e)  {<a name="line.593"></a>
<FONT color="green">594</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.594"></a>
<FONT color="green">595</FONT>        }<a name="line.595"></a>
<FONT color="green">596</FONT>      }<a name="line.596"></a>
<FONT color="green">597</FONT>      /**<a name="line.597"></a>
<FONT color="green">598</FONT>       * Adds an organization to the table "tbl_ORGANIZATION" in the database.<a name="line.598"></a>
<FONT color="green">599</FONT>       * @param o Organization to add.<a name="line.599"></a>
<FONT color="green">600</FONT>       */<a name="line.600"></a>
<FONT color="green">601</FONT>      public void addOrganization(Organization o) {<a name="line.601"></a>
<FONT color="green">602</FONT>        try {<a name="line.602"></a>
<FONT color="green">603</FONT>          String sqlCmd = "INSERT INTO tbl_ORGANIZATION " +  <a name="line.603"></a>
<FONT color="green">604</FONT>                          "(o_name, o_type_id, o_contact_id, o_country_id, o_desc, o_homepage)" +<a name="line.604"></a>
<FONT color="green">605</FONT>                          "VALUES (?,?,?,?,?,?)";<a name="line.605"></a>
<FONT color="green">606</FONT>          PreparedStatement pStmt = conn.prepareStatement(sqlCmd);<a name="line.606"></a>
<FONT color="green">607</FONT>          pStmt.setString(1, o.getName().trim());<a name="line.607"></a>
<FONT color="green">608</FONT>          int otype_id = getOrganizationTypeId(o.getType().trim());<a name="line.608"></a>
<FONT color="green">609</FONT>          if (otype_id == -1) {<a name="line.609"></a>
<FONT color="green">610</FONT>            addOrganizationType(o.getType());<a name="line.610"></a>
<FONT color="green">611</FONT>            otype_id = getOrganizationTypeId(o.getType().trim());<a name="line.611"></a>
<FONT color="green">612</FONT>          }<a name="line.612"></a>
<FONT color="green">613</FONT>          pStmt.setInt(2, otype_id);<a name="line.613"></a>
<FONT color="green">614</FONT>          <a name="line.614"></a>
<FONT color="green">615</FONT>          int r_id = getResearcherId(o.getContact().trim());<a name="line.615"></a>
<FONT color="green">616</FONT>          if (r_id == -1) {<a name="line.616"></a>
<FONT color="green">617</FONT>            pStmt.setNull(3, Types.INTEGER);<a name="line.617"></a>
<FONT color="green">618</FONT>          }<a name="line.618"></a>
<FONT color="green">619</FONT>          else {<a name="line.619"></a>
<FONT color="green">620</FONT>            pStmt.setInt(3, r_id);<a name="line.620"></a>
<FONT color="green">621</FONT>          }<a name="line.621"></a>
<FONT color="green">622</FONT>          <a name="line.622"></a>
<FONT color="green">623</FONT>          int country_id = getCountryId(o.getCountry().trim());<a name="line.623"></a>
<FONT color="green">624</FONT>          if (country_id == -1) {<a name="line.624"></a>
<FONT color="green">625</FONT>            addCountry(o.getCountry());<a name="line.625"></a>
<FONT color="green">626</FONT>            country_id = getCountryId(o.getCountry().trim());<a name="line.626"></a>
<FONT color="green">627</FONT>          }<a name="line.627"></a>
<FONT color="green">628</FONT>          pStmt.setInt(4, country_id);<a name="line.628"></a>
<FONT color="green">629</FONT>          pStmt.setString(5, o.getResearchDescription().trim());<a name="line.629"></a>
<FONT color="green">630</FONT>          pStmt.setString(6, o.getHomePage().trim());<a name="line.630"></a>
<FONT color="green">631</FONT>          pStmt.execute();<a name="line.631"></a>
<FONT color="green">632</FONT>          pStmt.close();<a name="line.632"></a>
<FONT color="green">633</FONT>    <a name="line.633"></a>
<FONT color="green">634</FONT>          <a name="line.634"></a>
<FONT color="green">635</FONT>          sqlCmd = "INSERT INTO tbl_RESEARCH_KEYWORD " +  <a name="line.635"></a>
<FONT color="green">636</FONT>                   "(o_id, r_keyword) VALUES (?,?)";<a name="line.636"></a>
<FONT color="green">637</FONT>          pStmt = conn.prepareStatement(sqlCmd);<a name="line.637"></a>
<FONT color="green">638</FONT>          int o_id = getOrganizationId(o.getName().trim());<a name="line.638"></a>
<FONT color="green">639</FONT>          if (o_id == -1) {<a name="line.639"></a>
<FONT color="green">640</FONT>            throw new InvalidOrganizationNameException(o.getName());<a name="line.640"></a>
<FONT color="green">641</FONT>          }<a name="line.641"></a>
<FONT color="green">642</FONT>          else {<a name="line.642"></a>
<FONT color="green">643</FONT>            for (String keyword : o.getResearchKeywords().getResearchKeyword()) {<a name="line.643"></a>
<FONT color="green">644</FONT>              pStmt.setInt(1, o_id);<a name="line.644"></a>
<FONT color="green">645</FONT>              pStmt.setString(2, keyword.trim());<a name="line.645"></a>
<FONT color="green">646</FONT>              pStmt.execute();<a name="line.646"></a>
<FONT color="green">647</FONT>            }<a name="line.647"></a>
<FONT color="green">648</FONT>          }<a name="line.648"></a>
<FONT color="green">649</FONT>          <a name="line.649"></a>
<FONT color="green">650</FONT>          <a name="line.650"></a>
<FONT color="green">651</FONT>          sqlCmd = "INSERT INTO tbl_RESEARCHER_ORGANIZATION " +  <a name="line.651"></a>
<FONT color="green">652</FONT>                   "(r_id, o_id) VALUES (?,?)";<a name="line.652"></a>
<FONT color="green">653</FONT>          pStmt = conn.prepareStatement(sqlCmd);<a name="line.653"></a>
<FONT color="green">654</FONT>          for (String researcher : o.getAffiliatedResearchers().getAffiliatedResearcher()) {<a name="line.654"></a>
<FONT color="green">655</FONT>            r_id = getResearcherId(researcher);<a name="line.655"></a>
<FONT color="green">656</FONT>            if (r_id == -1) {<a name="line.656"></a>
<FONT color="green">657</FONT>              throw new InvalidResearcherNameException(researcher);<a name="line.657"></a>
<FONT color="green">658</FONT>            }<a name="line.658"></a>
<FONT color="green">659</FONT>            else {<a name="line.659"></a>
<FONT color="green">660</FONT>              pStmt.setInt(1, r_id);<a name="line.660"></a>
<FONT color="green">661</FONT>              pStmt.setInt(2, o_id);<a name="line.661"></a>
<FONT color="green">662</FONT>              pStmt.execute();<a name="line.662"></a>
<FONT color="green">663</FONT>            }<a name="line.663"></a>
<FONT color="green">664</FONT>          }<a name="line.664"></a>
<FONT color="green">665</FONT>        }<a name="line.665"></a>
<FONT color="green">666</FONT>        catch (Exception e)  {<a name="line.666"></a>
<FONT color="green">667</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.667"></a>
<FONT color="green">668</FONT>        }<a name="line.668"></a>
<FONT color="green">669</FONT>      }<a name="line.669"></a>
<FONT color="green">670</FONT>      /**<a name="line.670"></a>
<FONT color="green">671</FONT>       * Get a organization provided the database ID.<a name="line.671"></a>
<FONT color="green">672</FONT>       * @param id of the researcher.<a name="line.672"></a>
<FONT color="green">673</FONT>       * @return a researcher object.<a name="line.673"></a>
<FONT color="green">674</FONT>       * @throws Exception if error occurs.<a name="line.674"></a>
<FONT color="green">675</FONT>       */<a name="line.675"></a>
<FONT color="green">676</FONT>      public Organization getOrganization(int id) throws Exception {<a name="line.676"></a>
<FONT color="green">677</FONT>        PreparedStatement pStmt = null;<a name="line.677"></a>
<FONT color="green">678</FONT>        Organization o = new Organization();<a name="line.678"></a>
<FONT color="green">679</FONT>        ResultSet results = null;<a name="line.679"></a>
<FONT color="green">680</FONT>        try {<a name="line.680"></a>
<FONT color="green">681</FONT>          String sqlCmd = "SELECT o_name, otype_name, r_fname, r_mname, r_lname, " +<a name="line.681"></a>
<FONT color="green">682</FONT>                                "country_name, o_desc, o_homepage " + <a name="line.682"></a>
<FONT color="green">683</FONT>                                tbl + <a name="line.683"></a>
<FONT color="green">684</FONT>                          "LEFT OUTER JOIN tbl_RESEARCHER ON o_contact_id = r_id " +<a name="line.684"></a>
<FONT color="green">685</FONT>                          "LEFT OUTER JOIN tbl_ORGANIZATION_TYPE ON o_type_id = otype_id " +<a name="line.685"></a>
<FONT color="green">686</FONT>                          "LEFT OUTER JOIN tbl_COUNTRY ON o_country_id = country_id " + <a name="line.686"></a>
<FONT color="green">687</FONT>                          "WHERE o_id = ?";<a name="line.687"></a>
<FONT color="green">688</FONT>          //System.out.println("sqlCmd: " + sqlCmd);<a name="line.688"></a>
<FONT color="green">689</FONT>          pStmt = conn.prepareStatement(sqlCmd);<a name="line.689"></a>
<FONT color="green">690</FONT>          pStmt.setInt(1, id);<a name="line.690"></a>
<FONT color="green">691</FONT>          results = pStmt.executeQuery();<a name="line.691"></a>
<FONT color="green">692</FONT>          results.next();<a name="line.692"></a>
<FONT color="green">693</FONT>          //System.out.println("(id: " + id + ")\tResults: " + results.toString());<a name="line.693"></a>
<FONT color="green">694</FONT>          //System.out.println("Results fname: " + results.getString(r_fname));<a name="line.694"></a>
<FONT color="green">695</FONT>          <a name="line.695"></a>
<FONT color="green">696</FONT>          o.setName(results.getString("o_name"));<a name="line.696"></a>
<FONT color="green">697</FONT>          o.setType(results.getString("otype_name"));<a name="line.697"></a>
<FONT color="green">698</FONT>          o.setContact(results.getString(r_fname) + " " + <a name="line.698"></a>
<FONT color="green">699</FONT>                       results.getString(r_mname) + " " +<a name="line.699"></a>
<FONT color="green">700</FONT>                       results.getString(r_lname));<a name="line.700"></a>
<FONT color="green">701</FONT>          o.setCountry(results.getString("country_name"));<a name="line.701"></a>
<FONT color="green">702</FONT>          o.setResearchDescription(results.getString("o_desc"));<a name="line.702"></a>
<FONT color="green">703</FONT>          o.setHomePage(results.getString("o_homepage"));<a name="line.703"></a>
<FONT color="green">704</FONT>          <a name="line.704"></a>
<FONT color="green">705</FONT>          //Still need to add in code for multiple organizations<a name="line.705"></a>
<FONT color="green">706</FONT>          <a name="line.706"></a>
<FONT color="green">707</FONT>        }<a name="line.707"></a>
<FONT color="green">708</FONT>        catch (Exception e)  {<a name="line.708"></a>
<FONT color="green">709</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.709"></a>
<FONT color="green">710</FONT>          o = null;<a name="line.710"></a>
<FONT color="green">711</FONT>        }<a name="line.711"></a>
<FONT color="green">712</FONT>        finally {<a name="line.712"></a>
<FONT color="green">713</FONT>          results.close();<a name="line.713"></a>
<FONT color="green">714</FONT>          pStmt.close();<a name="line.714"></a>
<FONT color="green">715</FONT>        }<a name="line.715"></a>
<FONT color="green">716</FONT>        return o;<a name="line.716"></a>
<FONT color="green">717</FONT>      }<a name="line.717"></a>
<FONT color="green">718</FONT>      <a name="line.718"></a>
<FONT color="green">719</FONT>      /**<a name="line.719"></a>
<FONT color="green">720</FONT>       * Gets the database ID of an organization given their name<a name="line.720"></a>
<FONT color="green">721</FONT>       * from the "tbl_ORGANIZATION" table.<a name="line.721"></a>
<FONT color="green">722</FONT>       * @param name of the organization.<a name="line.722"></a>
<FONT color="green">723</FONT>       * @return the id number of the organization.<a name="line.723"></a>
<FONT color="green">724</FONT>       * @throws Exception if error occurs.<a name="line.724"></a>
<FONT color="green">725</FONT>       */<a name="line.725"></a>
<FONT color="green">726</FONT>      public int getOrganizationId(String name) throws Exception {<a name="line.726"></a>
<FONT color="green">727</FONT>        int id = -1;<a name="line.727"></a>
<FONT color="green">728</FONT>        PreparedStatement pStmt = null;<a name="line.728"></a>
<FONT color="green">729</FONT>        ResultSet results = null;<a name="line.729"></a>
<FONT color="green">730</FONT>        //System.out.println("\torgName: " + name);<a name="line.730"></a>
<FONT color="green">731</FONT>        try {<a name="line.731"></a>
<FONT color="green">732</FONT>          String sqlCmd = "SELECT o_id " + <a name="line.732"></a>
<FONT color="green">733</FONT>                          tbl +  <a name="line.733"></a>
<FONT color="green">734</FONT>                          "WHERE o_name = ?";<a name="line.734"></a>
<FONT color="green">735</FONT>          pStmt = conn.prepareStatement(sqlCmd);<a name="line.735"></a>
<FONT color="green">736</FONT>          pStmt.setString(1, name);<a name="line.736"></a>
<FONT color="green">737</FONT>          results = pStmt.executeQuery();<a name="line.737"></a>
<FONT color="green">738</FONT>          if (results.next()) {<a name="line.738"></a>
<FONT color="green">739</FONT>            id = results.getInt("o_id");<a name="line.739"></a>
<FONT color="green">740</FONT>          }<a name="line.740"></a>
<FONT color="green">741</FONT>          else {<a name="line.741"></a>
<FONT color="green">742</FONT>            id = -1;<a name="line.742"></a>
<FONT color="green">743</FONT>          }<a name="line.743"></a>
<FONT color="green">744</FONT>        }<a name="line.744"></a>
<FONT color="green">745</FONT>        catch (Exception e) {<a name="line.745"></a>
<FONT color="green">746</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.746"></a>
<FONT color="green">747</FONT>        }<a name="line.747"></a>
<FONT color="green">748</FONT>        finally {<a name="line.748"></a>
<FONT color="green">749</FONT>          results.close();<a name="line.749"></a>
<FONT color="green">750</FONT>          pStmt.close();<a name="line.750"></a>
<FONT color="green">751</FONT>        }<a name="line.751"></a>
<FONT color="green">752</FONT>        return id;<a name="line.752"></a>
<FONT color="green">753</FONT>      }<a name="line.753"></a>
<FONT color="green">754</FONT>      /**<a name="line.754"></a>
<FONT color="green">755</FONT>       * Gets a list of all organizations in the tbl_ORGANIZATION table<a name="line.755"></a>
<FONT color="green">756</FONT>       * @return an array list of organization objects.<a name="line.756"></a>
<FONT color="green">757</FONT>       * @throws Exception if error occurs.<a name="line.757"></a>
<FONT color="green">758</FONT>       */<a name="line.758"></a>
<FONT color="green">759</FONT>      public ArrayList&lt;Organization&gt; getOrganizationList() throws Exception {<a name="line.759"></a>
<FONT color="green">760</FONT>        ArrayList&lt;Organization&gt; list = new ArrayList&lt;Organization&gt;();<a name="line.760"></a>
<FONT color="green">761</FONT>        Organization o;<a name="line.761"></a>
<FONT color="green">762</FONT>        ResultSet o_results = null;<a name="line.762"></a>
<FONT color="green">763</FONT>        //ResultSet r_results = null;<a name="line.763"></a>
<FONT color="green">764</FONT>        try {<a name="line.764"></a>
<FONT color="green">765</FONT>          String sqlCmd = "SELECT o_id, o_name, otype_name, r_fname, r_mname, r_lname, " +<a name="line.765"></a>
<FONT color="green">766</FONT>                          "country_name, o_desc, o_homepage " +<a name="line.766"></a>
<FONT color="green">767</FONT>                          tbl + <a name="line.767"></a>
<FONT color="green">768</FONT>                          "LEFT OUTER JOIN tbl_RESEARCHER ON o_contact_id = r_id " +<a name="line.768"></a>
<FONT color="green">769</FONT>                          "LEFT OUTER JOIN tbl_ORGANIZATION_TYPE ON o_type_id = otype_id " +<a name="line.769"></a>
<FONT color="green">770</FONT>                          "LEFT OUTER JOIN tbl_COUNTRY ON o_country_id = country_id";<a name="line.770"></a>
<FONT color="green">771</FONT>          stmt = conn.createStatement();<a name="line.771"></a>
<FONT color="green">772</FONT>          o_results = stmt.executeQuery(sqlCmd);<a name="line.772"></a>
<FONT color="green">773</FONT>          while (o_results.next()) {<a name="line.773"></a>
<FONT color="green">774</FONT>            o = new Organization();<a name="line.774"></a>
<FONT color="green">775</FONT>           <a name="line.775"></a>
<FONT color="green">776</FONT>            o.setName(o_results.getString("o_name"));<a name="line.776"></a>
<FONT color="green">777</FONT>            o.setType(o_results.getString("otype_name"));<a name="line.777"></a>
<FONT color="green">778</FONT>            o.setContact(getFullName(o_results.getString("r_fname"),<a name="line.778"></a>
<FONT color="green">779</FONT>                                     o_results.getString("r_mname"),<a name="line.779"></a>
<FONT color="green">780</FONT>                                     o_results.getString("r_lname")));<a name="line.780"></a>
<FONT color="green">781</FONT>            o.setCountry(o_results.getString("country_name"));<a name="line.781"></a>
<FONT color="green">782</FONT>            o.setResearchDescription(o_results.getString("o_desc"));<a name="line.782"></a>
<FONT color="green">783</FONT>            o.setHomePage(o_results.getString("o_homepage"));<a name="line.783"></a>
<FONT color="green">784</FONT>            //TODO getResearcherOrganizationList()<a name="line.784"></a>
<FONT color="green">785</FONT>            //TODO getResearcherOrganizationList(String type, int id)<a name="line.785"></a>
<FONT color="green">786</FONT>            //TODO getResearchKeywordList(int orgId)<a name="line.786"></a>
<FONT color="green">787</FONT>            list.add(o);<a name="line.787"></a>
<FONT color="green">788</FONT>          }<a name="line.788"></a>
<FONT color="green">789</FONT>        }<a name="line.789"></a>
<FONT color="green">790</FONT>        catch (Exception e)  {<a name="line.790"></a>
<FONT color="green">791</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.791"></a>
<FONT color="green">792</FONT>          list = null;<a name="line.792"></a>
<FONT color="green">793</FONT>        }<a name="line.793"></a>
<FONT color="green">794</FONT>        finally {<a name="line.794"></a>
<FONT color="green">795</FONT>          o_results.close();<a name="line.795"></a>
<FONT color="green">796</FONT>          stmt.close();<a name="line.796"></a>
<FONT color="green">797</FONT>        }<a name="line.797"></a>
<FONT color="green">798</FONT>        return list;<a name="line.798"></a>
<FONT color="green">799</FONT>        <a name="line.799"></a>
<FONT color="green">800</FONT>      }<a name="line.800"></a>
<FONT color="green">801</FONT>      /**<a name="line.801"></a>
<FONT color="green">802</FONT>       * Gets a list of the names of all organizations in the tbl_ORGANIZATION <a name="line.802"></a>
<FONT color="green">803</FONT>       * table.<a name="line.803"></a>
<FONT color="green">804</FONT>       * @return List of organization names.<a name="line.804"></a>
<FONT color="green">805</FONT>       * @throws SQLException If error occurs.<a name="line.805"></a>
<FONT color="green">806</FONT>       */<a name="line.806"></a>
<FONT color="green">807</FONT>      public List&lt;String&gt; getOrganizationNameList() throws SQLException {<a name="line.807"></a>
<FONT color="green">808</FONT>        ArrayList&lt;String&gt; list = new ArrayList&lt;String&gt;();<a name="line.808"></a>
<FONT color="green">809</FONT>        PreparedStatement pStmt = null;<a name="line.809"></a>
<FONT color="green">810</FONT>        ResultSet results = null;<a name="line.810"></a>
<FONT color="green">811</FONT>        try {<a name="line.811"></a>
<FONT color="green">812</FONT>          String sqlCmd = "SELECT o_name " + <a name="line.812"></a>
<FONT color="green">813</FONT>                          tbl;  <a name="line.813"></a>
<FONT color="green">814</FONT>          pStmt = conn.prepareStatement(sqlCmd);<a name="line.814"></a>
<FONT color="green">815</FONT>          results = pStmt.executeQuery();<a name="line.815"></a>
<FONT color="green">816</FONT>          while (results.next()) {<a name="line.816"></a>
<FONT color="green">817</FONT>            list.add(results.getString("o_name"));<a name="line.817"></a>
<FONT color="green">818</FONT>          }<a name="line.818"></a>
<FONT color="green">819</FONT>          return list;<a name="line.819"></a>
<FONT color="green">820</FONT>        }<a name="line.820"></a>
<FONT color="green">821</FONT>        catch (Exception e) {<a name="line.821"></a>
<FONT color="green">822</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.822"></a>
<FONT color="green">823</FONT>          return null;<a name="line.823"></a>
<FONT color="green">824</FONT>        }<a name="line.824"></a>
<FONT color="green">825</FONT>        finally {<a name="line.825"></a>
<FONT color="green">826</FONT>          pStmt.close();<a name="line.826"></a>
<FONT color="green">827</FONT>          results.close();<a name="line.827"></a>
<FONT color="green">828</FONT>        }<a name="line.828"></a>
<FONT color="green">829</FONT>      }<a name="line.829"></a>
<FONT color="green">830</FONT>      /**<a name="line.830"></a>
<FONT color="green">831</FONT>       * Creates the tbl_ORGANIZATION_TYPE table.<a name="line.831"></a>
<FONT color="green">832</FONT>       */<a name="line.832"></a>
<FONT color="green">833</FONT>      public void createOrganizationTypeTable() {<a name="line.833"></a>
<FONT color="green">834</FONT>        try {<a name="line.834"></a>
<FONT color="green">835</FONT>          String sqlCmd = "CREATE TABLE tbl_ORGANIZATION_TYPE (" +<a name="line.835"></a>
<FONT color="green">836</FONT>                          "   otype_id          INT           GENERATED ALWAYS AS IDENTITY" + <a name="line.836"></a>
<FONT color="green">837</FONT>                          "                                   CONSTRAINT otype_id_pk PRIMARY KEY," +<a name="line.837"></a>
<FONT color="green">838</FONT>                          "   otype_name        VARCHAR(64)   NOT NULL" +<a name="line.838"></a>
<FONT color="green">839</FONT>                          ")";<a name="line.839"></a>
<FONT color="green">840</FONT>          stmt = conn.createStatement();<a name="line.840"></a>
<FONT color="green">841</FONT>          stmt.execute(sqlCmd);<a name="line.841"></a>
<FONT color="green">842</FONT>          stmt.close();<a name="line.842"></a>
<FONT color="green">843</FONT>        }<a name="line.843"></a>
<FONT color="green">844</FONT>        catch (Exception e) {<a name="line.844"></a>
<FONT color="green">845</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.845"></a>
<FONT color="green">846</FONT>        }<a name="line.846"></a>
<FONT color="green">847</FONT>      }<a name="line.847"></a>
<FONT color="green">848</FONT>      /**<a name="line.848"></a>
<FONT color="green">849</FONT>       * Drops the tbl_ORGANIZATION_TYPE Table.<a name="line.849"></a>
<FONT color="green">850</FONT>       */<a name="line.850"></a>
<FONT color="green">851</FONT>      public void dropOrganizationTypeTable() {<a name="line.851"></a>
<FONT color="green">852</FONT>        try {<a name="line.852"></a>
<FONT color="green">853</FONT>          String sqlCmd = "DROP TABLE tbl_ORGANIZATION_TYPE";<a name="line.853"></a>
<FONT color="green">854</FONT>          stmt = conn.createStatement();<a name="line.854"></a>
<FONT color="green">855</FONT>          stmt.execute(sqlCmd);<a name="line.855"></a>
<FONT color="green">856</FONT>          stmt.close();<a name="line.856"></a>
<FONT color="green">857</FONT>        }<a name="line.857"></a>
<FONT color="green">858</FONT>        catch (Exception e)  {<a name="line.858"></a>
<FONT color="green">859</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.859"></a>
<FONT color="green">860</FONT>        }<a name="line.860"></a>
<FONT color="green">861</FONT>      }<a name="line.861"></a>
<FONT color="green">862</FONT>      /**<a name="line.862"></a>
<FONT color="green">863</FONT>       * Adds an organization type to the table "tbl_ORGANIZATION_TYPE" in the database.<a name="line.863"></a>
<FONT color="green">864</FONT>       * @param type organization type to add.<a name="line.864"></a>
<FONT color="green">865</FONT>       */<a name="line.865"></a>
<FONT color="green">866</FONT>      public void addOrganizationType(String type) {<a name="line.866"></a>
<FONT color="green">867</FONT>        try {<a name="line.867"></a>
<FONT color="green">868</FONT>          String sqlCmd = "INSERT INTO tbl_ORGANIZATION_TYPE " +  <a name="line.868"></a>
<FONT color="green">869</FONT>                          "(otype_name)" +<a name="line.869"></a>
<FONT color="green">870</FONT>                          "VALUES (?)";<a name="line.870"></a>
<FONT color="green">871</FONT>          PreparedStatement pStmt = conn.prepareStatement(sqlCmd);<a name="line.871"></a>
<FONT color="green">872</FONT>          pStmt.setString(1, type.trim());<a name="line.872"></a>
<FONT color="green">873</FONT>          pStmt.execute();<a name="line.873"></a>
<FONT color="green">874</FONT>          pStmt.close();<a name="line.874"></a>
<FONT color="green">875</FONT>        }<a name="line.875"></a>
<FONT color="green">876</FONT>        catch (Exception e)  {<a name="line.876"></a>
<FONT color="green">877</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.877"></a>
<FONT color="green">878</FONT>        }<a name="line.878"></a>
<FONT color="green">879</FONT>      }<a name="line.879"></a>
<FONT color="green">880</FONT>     <a name="line.880"></a>
<FONT color="green">881</FONT>      /**<a name="line.881"></a>
<FONT color="green">882</FONT>       * Gets the database ID of an organization type given the type name<a name="line.882"></a>
<FONT color="green">883</FONT>       * from the "tbl_ORGANIZATION_TYPE" table.<a name="line.883"></a>
<FONT color="green">884</FONT>       * @param type Type name.<a name="line.884"></a>
<FONT color="green">885</FONT>       * @return a number.<a name="line.885"></a>
<FONT color="green">886</FONT>       * @throws Exception if error occurs.<a name="line.886"></a>
<FONT color="green">887</FONT>       */<a name="line.887"></a>
<FONT color="green">888</FONT>      public int getOrganizationTypeId(String type) throws Exception {<a name="line.888"></a>
<FONT color="green">889</FONT>        int id = -1;<a name="line.889"></a>
<FONT color="green">890</FONT>        PreparedStatement pStmt = null;<a name="line.890"></a>
<FONT color="green">891</FONT>        ResultSet results = null;<a name="line.891"></a>
<FONT color="green">892</FONT>        try {<a name="line.892"></a>
<FONT color="green">893</FONT>          String sqlCmd = "SELECT otype_id " + <a name="line.893"></a>
<FONT color="green">894</FONT>                          "FROM tbl_ORGANIZATION_TYPE " +  <a name="line.894"></a>
<FONT color="green">895</FONT>                          "WHERE otype_name = ?";<a name="line.895"></a>
<FONT color="green">896</FONT>          pStmt = conn.prepareStatement(sqlCmd);<a name="line.896"></a>
<FONT color="green">897</FONT>          pStmt.setString(1, type);<a name="line.897"></a>
<FONT color="green">898</FONT>          results = pStmt.executeQuery();<a name="line.898"></a>
<FONT color="green">899</FONT>          if (results.next()) {<a name="line.899"></a>
<FONT color="green">900</FONT>            id = results.getInt("otype_id");<a name="line.900"></a>
<FONT color="green">901</FONT>          }<a name="line.901"></a>
<FONT color="green">902</FONT>          else {<a name="line.902"></a>
<FONT color="green">903</FONT>            id = -1;<a name="line.903"></a>
<FONT color="green">904</FONT>          }<a name="line.904"></a>
<FONT color="green">905</FONT>        }<a name="line.905"></a>
<FONT color="green">906</FONT>        catch (Exception e) {<a name="line.906"></a>
<FONT color="green">907</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.907"></a>
<FONT color="green">908</FONT>        }<a name="line.908"></a>
<FONT color="green">909</FONT>        finally {<a name="line.909"></a>
<FONT color="green">910</FONT>          results.close();<a name="line.910"></a>
<FONT color="green">911</FONT>          pStmt.close();<a name="line.911"></a>
<FONT color="green">912</FONT>        }<a name="line.912"></a>
<FONT color="green">913</FONT>        return id;<a name="line.913"></a>
<FONT color="green">914</FONT>      }<a name="line.914"></a>
<FONT color="green">915</FONT>      /**<a name="line.915"></a>
<FONT color="green">916</FONT>       * Creates the tbl_RESEARCH_KEYWORD Table.<a name="line.916"></a>
<FONT color="green">917</FONT>       */<a name="line.917"></a>
<FONT color="green">918</FONT>      public void createResearchKeywordTable() {<a name="line.918"></a>
<FONT color="green">919</FONT>        try {<a name="line.919"></a>
<FONT color="green">920</FONT>          String sqlCmd = "CREATE TABLE tbl_RESEARCH_KEYWORD (" +<a name="line.920"></a>
<FONT color="green">921</FONT>                          "   o_id          INT           NOT NULL" + <a name="line.921"></a>
<FONT color="green">922</FONT>                          "                               CONSTRAINT o_id_keyword_fk" +<a name="line.922"></a>
<FONT color="green">923</FONT>                          "                               REFERENCES tbl_ORGANIZATION," +<a name="line.923"></a>
<FONT color="green">924</FONT>                          "   r_keyword     VARCHAR(128)  NOT NULL," +<a name="line.924"></a>
<FONT color="green">925</FONT>                          <a name="line.925"></a>
<FONT color="green">926</FONT>                          "   CONSTRAINT o_id_k_pk        PRIMARY KEY(o_id, r_keyword)" +<a name="line.926"></a>
<FONT color="green">927</FONT>                          ")";<a name="line.927"></a>
<FONT color="green">928</FONT>          stmt = conn.createStatement();<a name="line.928"></a>
<FONT color="green">929</FONT>          stmt.execute(sqlCmd);<a name="line.929"></a>
<FONT color="green">930</FONT>          stmt.close();<a name="line.930"></a>
<FONT color="green">931</FONT>        }<a name="line.931"></a>
<FONT color="green">932</FONT>        catch (Exception e)  {<a name="line.932"></a>
<FONT color="green">933</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.933"></a>
<FONT color="green">934</FONT>        }<a name="line.934"></a>
<FONT color="green">935</FONT>      }<a name="line.935"></a>
<FONT color="green">936</FONT>      /**<a name="line.936"></a>
<FONT color="green">937</FONT>       * Drops the tbl_RESEARCH_KEYWORD Table.<a name="line.937"></a>
<FONT color="green">938</FONT>       */<a name="line.938"></a>
<FONT color="green">939</FONT>      public void dropResearchKeywordTable() {<a name="line.939"></a>
<FONT color="green">940</FONT>        try {<a name="line.940"></a>
<FONT color="green">941</FONT>          String sqlCmd = " DROP TABLE tbl_RESEARCH_KEYWORD";<a name="line.941"></a>
<FONT color="green">942</FONT>          stmt = conn.createStatement();<a name="line.942"></a>
<FONT color="green">943</FONT>          stmt.execute(sqlCmd);<a name="line.943"></a>
<FONT color="green">944</FONT>          stmt.close();<a name="line.944"></a>
<FONT color="green">945</FONT>          <a name="line.945"></a>
<FONT color="green">946</FONT>        }<a name="line.946"></a>
<FONT color="green">947</FONT>        catch (Exception e)  {<a name="line.947"></a>
<FONT color="green">948</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.948"></a>
<FONT color="green">949</FONT>        }<a name="line.949"></a>
<FONT color="green">950</FONT>      }<a name="line.950"></a>
<FONT color="green">951</FONT>      /**<a name="line.951"></a>
<FONT color="green">952</FONT>       * Adds a research keyword to the tbl_RESEARCH_KEYWORD <a name="line.952"></a>
<FONT color="green">953</FONT>       * table.<a name="line.953"></a>
<FONT color="green">954</FONT>       * @param orgId Id number of the organization<a name="line.954"></a>
<FONT color="green">955</FONT>       * @param keyword Keyword to add.<a name="line.955"></a>
<FONT color="green">956</FONT>       * @throws SQLException If error occurs.<a name="line.956"></a>
<FONT color="green">957</FONT>       */<a name="line.957"></a>
<FONT color="green">958</FONT>      public void addResearchKeyword(int orgId, String keyword) throws SQLException {<a name="line.958"></a>
<FONT color="green">959</FONT>        PreparedStatement pStmt = null;<a name="line.959"></a>
<FONT color="green">960</FONT>        try {<a name="line.960"></a>
<FONT color="green">961</FONT>          String sqlCmd = "INSERT INTO tbl_RESEARCH_KEYWORD " +  <a name="line.961"></a>
<FONT color="green">962</FONT>                          "(o_id, r_keyword)" +<a name="line.962"></a>
<FONT color="green">963</FONT>                          "VALUES (?,?)";<a name="line.963"></a>
<FONT color="green">964</FONT>          pStmt = conn.prepareStatement(sqlCmd);<a name="line.964"></a>
<FONT color="green">965</FONT>          pStmt.setInt(1, orgId);<a name="line.965"></a>
<FONT color="green">966</FONT>          pStmt.setString(2, keyword.trim());<a name="line.966"></a>
<FONT color="green">967</FONT>          pStmt.execute();<a name="line.967"></a>
<FONT color="green">968</FONT>        }<a name="line.968"></a>
<FONT color="green">969</FONT>        catch (Exception e) {<a name="line.969"></a>
<FONT color="green">970</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.970"></a>
<FONT color="green">971</FONT>        }<a name="line.971"></a>
<FONT color="green">972</FONT>        finally {<a name="line.972"></a>
<FONT color="green">973</FONT>          pStmt.close();<a name="line.973"></a>
<FONT color="green">974</FONT>        }<a name="line.974"></a>
<FONT color="green">975</FONT>      }<a name="line.975"></a>
<FONT color="green">976</FONT>      /**<a name="line.976"></a>
<FONT color="green">977</FONT>       * Creates the tbl_RESEARCHER_ORGANIZATION Table.<a name="line.977"></a>
<FONT color="green">978</FONT>       */<a name="line.978"></a>
<FONT color="green">979</FONT>      public void createResearcherOrganizationTable() {<a name="line.979"></a>
<FONT color="green">980</FONT>        try {<a name="line.980"></a>
<FONT color="green">981</FONT>          String sqlCmd = "CREATE TABLE tbl_RESEARCHER_ORGANIZATION (" +<a name="line.981"></a>
<FONT color="green">982</FONT>                          "   r_id          INT           NOT NULL" + <a name="line.982"></a>
<FONT color="green">983</FONT>                          "                               CONSTRAINT o_id_fk" +<a name="line.983"></a>
<FONT color="green">984</FONT>                          "                               REFERENCES tbl_ORGANIZATION," +<a name="line.984"></a>
<FONT color="green">985</FONT>                          "   o_id          INT           NOT NULL" +<a name="line.985"></a>
<FONT color="green">986</FONT>                          "                               CONSTRAINT r_id_fk" +<a name="line.986"></a>
<FONT color="green">987</FONT>                          "                               REFERENCES tbl_RESEARCHER," +<a name="line.987"></a>
<FONT color="green">988</FONT>                          <a name="line.988"></a>
<FONT color="green">989</FONT>                          "   CONSTRAINT r_o_id_pk        PRIMARY KEY(r_id, o_id)" +<a name="line.989"></a>
<FONT color="green">990</FONT>                          ")";<a name="line.990"></a>
<FONT color="green">991</FONT>          stmt = conn.createStatement();<a name="line.991"></a>
<FONT color="green">992</FONT>          stmt.execute(sqlCmd);<a name="line.992"></a>
<FONT color="green">993</FONT>          stmt.close();<a name="line.993"></a>
<FONT color="green">994</FONT>        }<a name="line.994"></a>
<FONT color="green">995</FONT>        catch (Exception e)  {<a name="line.995"></a>
<FONT color="green">996</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.996"></a>
<FONT color="green">997</FONT>        }<a name="line.997"></a>
<FONT color="green">998</FONT>      }<a name="line.998"></a>
<FONT color="green">999</FONT>      /**<a name="line.999"></a>
<FONT color="green">1000</FONT>       * Drops the tbl_RESEARCHER_ORGANIZATION Table.<a name="line.1000"></a>
<FONT color="green">1001</FONT>       */<a name="line.1001"></a>
<FONT color="green">1002</FONT>      public void dropResearcherOrganizationTable() {<a name="line.1002"></a>
<FONT color="green">1003</FONT>        try {<a name="line.1003"></a>
<FONT color="green">1004</FONT>          String sqlCmd = "DROP TABLE tbl_RESEARCHER_ORGANIZATION";<a name="line.1004"></a>
<FONT color="green">1005</FONT>          stmt = conn.createStatement();<a name="line.1005"></a>
<FONT color="green">1006</FONT>          stmt.execute(sqlCmd);<a name="line.1006"></a>
<FONT color="green">1007</FONT>          stmt.close();<a name="line.1007"></a>
<FONT color="green">1008</FONT>        }<a name="line.1008"></a>
<FONT color="green">1009</FONT>        catch (Exception e)  {<a name="line.1009"></a>
<FONT color="green">1010</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1010"></a>
<FONT color="green">1011</FONT>        }<a name="line.1011"></a>
<FONT color="green">1012</FONT>      }<a name="line.1012"></a>
<FONT color="green">1013</FONT>      /**<a name="line.1013"></a>
<FONT color="green">1014</FONT>       * Adds an entity to the tbl_RESEARCHER_ORGANIZATION Table.<a name="line.1014"></a>
<FONT color="green">1015</FONT>       * @param rId Researcher Id number.<a name="line.1015"></a>
<FONT color="green">1016</FONT>       * @param oId Organization Id number.<a name="line.1016"></a>
<FONT color="green">1017</FONT>       */<a name="line.1017"></a>
<FONT color="green">1018</FONT>      public void addResearcherOrganization(int rId, int oId) {<a name="line.1018"></a>
<FONT color="green">1019</FONT>        try {<a name="line.1019"></a>
<FONT color="green">1020</FONT>          String sqlCmd = "INSERT INTO tbl_RESEARCHER_ORGANIZATION " +<a name="line.1020"></a>
<FONT color="green">1021</FONT>                                "(r_id, o_id) " +<a name="line.1021"></a>
<FONT color="green">1022</FONT>                                "VALUES (?,?)";<a name="line.1022"></a>
<FONT color="green">1023</FONT>          PreparedStatement pStmt = conn.prepareStatement(sqlCmd);<a name="line.1023"></a>
<FONT color="green">1024</FONT>          pStmt.setInt(1, rId);<a name="line.1024"></a>
<FONT color="green">1025</FONT>          pStmt.setInt(2, oId);<a name="line.1025"></a>
<FONT color="green">1026</FONT>          pStmt.execute();<a name="line.1026"></a>
<FONT color="green">1027</FONT>          pStmt.close();<a name="line.1027"></a>
<FONT color="green">1028</FONT>        }<a name="line.1028"></a>
<FONT color="green">1029</FONT>        catch (Exception e)  {<a name="line.1029"></a>
<FONT color="green">1030</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1030"></a>
<FONT color="green">1031</FONT>        }<a name="line.1031"></a>
<FONT color="green">1032</FONT>      }<a name="line.1032"></a>
<FONT color="green">1033</FONT>      /**<a name="line.1033"></a>
<FONT color="green">1034</FONT>       * Gets a from the tbl_RESEARCHER_ORGANIZATION table.<a name="line.1034"></a>
<FONT color="green">1035</FONT>       * @return map&lt;Researcher, Organization&gt;<a name="line.1035"></a>
<FONT color="green">1036</FONT>       * @throws SQLException If error occurs.<a name="line.1036"></a>
<FONT color="green">1037</FONT>       */<a name="line.1037"></a>
<FONT color="green">1038</FONT>      public Map&lt;Researcher, Organization&gt; getResearcherOrganizationList() throws SQLException {<a name="line.1038"></a>
<FONT color="green">1039</FONT>        Map&lt;Researcher, Organization&gt; map = new HashMap&lt;Researcher, Organization&gt;();<a name="line.1039"></a>
<FONT color="green">1040</FONT>        ResultSet results = null;<a name="line.1040"></a>
<FONT color="green">1041</FONT>        try {<a name="line.1041"></a>
<FONT color="green">1042</FONT>          String sqlCmd = "SELECT r_id, o_id " +<a name="line.1042"></a>
<FONT color="green">1043</FONT>                          "FROM tbl_RESEARCHER_ORGANIZATION ";<a name="line.1043"></a>
<FONT color="green">1044</FONT>          stmt = conn.createStatement();<a name="line.1044"></a>
<FONT color="green">1045</FONT>          results = stmt.executeQuery(sqlCmd);<a name="line.1045"></a>
<FONT color="green">1046</FONT>          while (results.next()) {<a name="line.1046"></a>
<FONT color="green">1047</FONT>            int r_id = results.getInt("r_id");<a name="line.1047"></a>
<FONT color="green">1048</FONT>            int o_id = results.getInt("o_id");<a name="line.1048"></a>
<FONT color="green">1049</FONT>            Researcher r = getResearcher(r_id);<a name="line.1049"></a>
<FONT color="green">1050</FONT>            Organization o = getOrganization(o_id);<a name="line.1050"></a>
<FONT color="green">1051</FONT>            map.put(r, o);<a name="line.1051"></a>
<FONT color="green">1052</FONT>          }<a name="line.1052"></a>
<FONT color="green">1053</FONT>          return map;<a name="line.1053"></a>
<FONT color="green">1054</FONT>        }<a name="line.1054"></a>
<FONT color="green">1055</FONT>        catch (Exception e)  {<a name="line.1055"></a>
<FONT color="green">1056</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1056"></a>
<FONT color="green">1057</FONT>          return null;<a name="line.1057"></a>
<FONT color="green">1058</FONT>        }<a name="line.1058"></a>
<FONT color="green">1059</FONT>        finally {<a name="line.1059"></a>
<FONT color="green">1060</FONT>          stmt.close();<a name="line.1060"></a>
<FONT color="green">1061</FONT>          results.close();<a name="line.1061"></a>
<FONT color="green">1062</FONT>        }<a name="line.1062"></a>
<FONT color="green">1063</FONT>      }<a name="line.1063"></a>
<FONT color="green">1064</FONT>      /**<a name="line.1064"></a>
<FONT color="green">1065</FONT>       * Creates the tbl_COLLABORATION Table.<a name="line.1065"></a>
<FONT color="green">1066</FONT>       */<a name="line.1066"></a>
<FONT color="green">1067</FONT>      public void createCollaborationTable() {<a name="line.1067"></a>
<FONT color="green">1068</FONT>        try {<a name="line.1068"></a>
<FONT color="green">1069</FONT>          String sqlCmd = "CREATE TABLE tbl_COLLABORATION (" +<a name="line.1069"></a>
<FONT color="green">1070</FONT>                          "   c_id          INT           GENERATED ALWAYS AS IDENTITY" + <a name="line.1070"></a>
<FONT color="green">1071</FONT>                          "                               CONSTRAINT c_id_pk PRIMARY KEY," +<a name="line.1071"></a>
<FONT color="green">1072</FONT>                          "   c_name        VARCHAR(128)   NOT NULL," +<a name="line.1072"></a>
<FONT color="green">1073</FONT>                          "   c_desc        VARCHAR(1028)  " +<a name="line.1073"></a>
<FONT color="green">1074</FONT>                          ")";<a name="line.1074"></a>
<FONT color="green">1075</FONT>          stmt = conn.createStatement();<a name="line.1075"></a>
<FONT color="green">1076</FONT>          stmt.execute(sqlCmd);<a name="line.1076"></a>
<FONT color="green">1077</FONT>          stmt.close();<a name="line.1077"></a>
<FONT color="green">1078</FONT>        }<a name="line.1078"></a>
<FONT color="green">1079</FONT>        catch (Exception e)  {<a name="line.1079"></a>
<FONT color="green">1080</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1080"></a>
<FONT color="green">1081</FONT>        }<a name="line.1081"></a>
<FONT color="green">1082</FONT>      }<a name="line.1082"></a>
<FONT color="green">1083</FONT>      /**<a name="line.1083"></a>
<FONT color="green">1084</FONT>       * Drops the tbl_COLLABORATION Table.<a name="line.1084"></a>
<FONT color="green">1085</FONT>       */<a name="line.1085"></a>
<FONT color="green">1086</FONT>      public void dropCollaborationTable() {<a name="line.1086"></a>
<FONT color="green">1087</FONT>        try {<a name="line.1087"></a>
<FONT color="green">1088</FONT>          String sqlCmd = " DROP TABLE tbl_COLLABORATION";<a name="line.1088"></a>
<FONT color="green">1089</FONT>          stmt = conn.createStatement();<a name="line.1089"></a>
<FONT color="green">1090</FONT>          stmt.execute(sqlCmd);<a name="line.1090"></a>
<FONT color="green">1091</FONT>          stmt.close();<a name="line.1091"></a>
<FONT color="green">1092</FONT>          <a name="line.1092"></a>
<FONT color="green">1093</FONT>        }<a name="line.1093"></a>
<FONT color="green">1094</FONT>        catch (Exception e)  {<a name="line.1094"></a>
<FONT color="green">1095</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1095"></a>
<FONT color="green">1096</FONT>        }<a name="line.1096"></a>
<FONT color="green">1097</FONT>      }<a name="line.1097"></a>
<FONT color="green">1098</FONT>      /**<a name="line.1098"></a>
<FONT color="green">1099</FONT>       * Adds a collaboration to the table "tbl_COLLABORATION" in the database.<a name="line.1099"></a>
<FONT color="green">1100</FONT>       * @param c Collaboration to add.<a name="line.1100"></a>
<FONT color="green">1101</FONT>       */<a name="line.1101"></a>
<FONT color="green">1102</FONT>      @SuppressWarnings("deprecation")<a name="line.1102"></a>
<FONT color="green">1103</FONT>      public void addCollaboration(Collaboration c) {<a name="line.1103"></a>
<FONT color="green">1104</FONT>        try {<a name="line.1104"></a>
<FONT color="green">1105</FONT>          int c_id = -1;<a name="line.1105"></a>
<FONT color="green">1106</FONT>          String sqlCmd = "INSERT INTO tbl_COLLABORATION " + <a name="line.1106"></a>
<FONT color="green">1107</FONT>                          "(c_name, c_desc) VALUES (?,?)";<a name="line.1107"></a>
<FONT color="green">1108</FONT>          PreparedStatement pStmt = conn.prepareStatement(sqlCmd);<a name="line.1108"></a>
<FONT color="green">1109</FONT>          pStmt.setString(1, c.getName().trim());<a name="line.1109"></a>
<FONT color="green">1110</FONT>          pStmt.setString(2, c.getDescription().trim());<a name="line.1110"></a>
<FONT color="green">1111</FONT>          pStmt.execute();<a name="line.1111"></a>
<FONT color="green">1112</FONT>          pStmt.close();<a name="line.1112"></a>
<FONT color="green">1113</FONT>          <a name="line.1113"></a>
<FONT color="green">1114</FONT>          c_id = getCollaborationId(c.getName().trim());<a name="line.1114"></a>
<FONT color="green">1115</FONT>          for (String org : c.getCollaboratingOrganizations().getCollaboratingOrganization()) {<a name="line.1115"></a>
<FONT color="green">1116</FONT>            int o_id = getOrganizationId(org.trim());<a name="line.1116"></a>
<FONT color="green">1117</FONT>            if (o_id == -1) {<a name="line.1117"></a>
<FONT color="green">1118</FONT>              throw new InvalidOrganizationNameException(org);<a name="line.1118"></a>
<FONT color="green">1119</FONT>            }<a name="line.1119"></a>
<FONT color="green">1120</FONT>            else {<a name="line.1120"></a>
<FONT color="green">1121</FONT>              sqlCmd = "INSERT INTO tbl_COLLABORATING_ORGANIZATION " +  <a name="line.1121"></a>
<FONT color="green">1122</FONT>                       "(c_id, o_id) VALUES (?,?)";<a name="line.1122"></a>
<FONT color="green">1123</FONT>              pStmt = conn.prepareStatement(sqlCmd);<a name="line.1123"></a>
<FONT color="green">1124</FONT>              pStmt.setInt(1, c_id);<a name="line.1124"></a>
<FONT color="green">1125</FONT>              pStmt.setInt(2, o_id);<a name="line.1125"></a>
<FONT color="green">1126</FONT>              pStmt.execute();<a name="line.1126"></a>
<FONT color="green">1127</FONT>              pStmt.close();<a name="line.1127"></a>
<FONT color="green">1128</FONT>            }<a name="line.1128"></a>
<FONT color="green">1129</FONT>          }<a name="line.1129"></a>
<FONT color="green">1130</FONT>          for (String type : c.getOutcomeTypes().getOutcomeType()) {<a name="line.1130"></a>
<FONT color="green">1131</FONT>            sqlCmd = "INSERT INTO tbl_OUTCOME_TYPE " +  <a name="line.1131"></a>
<FONT color="green">1132</FONT>                     "(c_id, outcome_type) VALUES (?,?)";<a name="line.1132"></a>
<FONT color="green">1133</FONT>            pStmt = conn.prepareStatement(sqlCmd);<a name="line.1133"></a>
<FONT color="green">1134</FONT>            pStmt.setInt(1, c_id);<a name="line.1134"></a>
<FONT color="green">1135</FONT>            pStmt.setString(2, type.trim());<a name="line.1135"></a>
<FONT color="green">1136</FONT>            pStmt.execute();<a name="line.1136"></a>
<FONT color="green">1137</FONT>            pStmt.close();<a name="line.1137"></a>
<FONT color="green">1138</FONT>            <a name="line.1138"></a>
<FONT color="green">1139</FONT>          }<a name="line.1139"></a>
<FONT color="green">1140</FONT>          for (String type : c.getCollaborationTypes().getCollaborationType()) {<a name="line.1140"></a>
<FONT color="green">1141</FONT>            sqlCmd = "INSERT INTO tbl_COLLABORATION_TYPE " + <a name="line.1141"></a>
<FONT color="green">1142</FONT>                     "(c_id, c_type) VALUES (?,?)";<a name="line.1142"></a>
<FONT color="green">1143</FONT>            pStmt = conn.prepareStatement(sqlCmd);<a name="line.1143"></a>
<FONT color="green">1144</FONT>            pStmt.setInt(1, c_id);<a name="line.1144"></a>
<FONT color="green">1145</FONT>            pStmt.setString(2, type.trim());<a name="line.1145"></a>
<FONT color="green">1146</FONT>            pStmt.execute();<a name="line.1146"></a>
<FONT color="green">1147</FONT>            pStmt.close();<a name="line.1147"></a>
<FONT color="green">1148</FONT>          }<a name="line.1148"></a>
<FONT color="green">1149</FONT>          for (BigInteger year : c.getYears().getYear()) {<a name="line.1149"></a>
<FONT color="green">1150</FONT>            sqlCmd = "INSERT INTO tbl_YEAR " +  <a name="line.1150"></a>
<FONT color="green">1151</FONT>                     "(c_id, y_type) VALUES (?,?)";<a name="line.1151"></a>
<FONT color="green">1152</FONT>            pStmt = conn.prepareStatement(sqlCmd);<a name="line.1152"></a>
<FONT color="green">1153</FONT>            pStmt.setInt(1, c_id);<a name="line.1153"></a>
<FONT color="green">1154</FONT>            Date date = new Date(year.intValue(), 1, 1);<a name="line.1154"></a>
<FONT color="green">1155</FONT>            pStmt.setDate(2, date);<a name="line.1155"></a>
<FONT color="green">1156</FONT>            pStmt.execute();<a name="line.1156"></a>
<FONT color="green">1157</FONT>            pStmt.close();<a name="line.1157"></a>
<FONT color="green">1158</FONT>          }<a name="line.1158"></a>
<FONT color="green">1159</FONT>        }<a name="line.1159"></a>
<FONT color="green">1160</FONT>        catch (Exception e)  {<a name="line.1160"></a>
<FONT color="green">1161</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1161"></a>
<FONT color="green">1162</FONT>        }<a name="line.1162"></a>
<FONT color="green">1163</FONT>      }<a name="line.1163"></a>
<FONT color="green">1164</FONT>      /**<a name="line.1164"></a>
<FONT color="green">1165</FONT>       * Gets the database ID of an collaboration given their name<a name="line.1165"></a>
<FONT color="green">1166</FONT>       * from the "tbl_COLLABORATION" table.<a name="line.1166"></a>
<FONT color="green">1167</FONT>       * @param name Name of the collaboration.<a name="line.1167"></a>
<FONT color="green">1168</FONT>       * @return a number.<a name="line.1168"></a>
<FONT color="green">1169</FONT>       * @throws Exception if error occurs.<a name="line.1169"></a>
<FONT color="green">1170</FONT>       */<a name="line.1170"></a>
<FONT color="green">1171</FONT>      public int getCollaborationId(String name) throws Exception {<a name="line.1171"></a>
<FONT color="green">1172</FONT>        int id = -1;<a name="line.1172"></a>
<FONT color="green">1173</FONT>        PreparedStatement pStmt = null;<a name="line.1173"></a>
<FONT color="green">1174</FONT>        ResultSet results = null;<a name="line.1174"></a>
<FONT color="green">1175</FONT>        try {<a name="line.1175"></a>
<FONT color="green">1176</FONT>          String sqlCmd = "SELECT c_id " + <a name="line.1176"></a>
<FONT color="green">1177</FONT>                          "FROM tbl_COLLABORATION " + <a name="line.1177"></a>
<FONT color="green">1178</FONT>                          "WHERE c_name = ?";<a name="line.1178"></a>
<FONT color="green">1179</FONT>          pStmt = conn.prepareStatement(sqlCmd);<a name="line.1179"></a>
<FONT color="green">1180</FONT>          pStmt.setString(1, name);<a name="line.1180"></a>
<FONT color="green">1181</FONT>          results = pStmt.executeQuery();<a name="line.1181"></a>
<FONT color="green">1182</FONT>          if (results.next()) {<a name="line.1182"></a>
<FONT color="green">1183</FONT>            id = results.getInt("c_id");<a name="line.1183"></a>
<FONT color="green">1184</FONT>          }<a name="line.1184"></a>
<FONT color="green">1185</FONT>          else {<a name="line.1185"></a>
<FONT color="green">1186</FONT>            id = -1;<a name="line.1186"></a>
<FONT color="green">1187</FONT>          }<a name="line.1187"></a>
<FONT color="green">1188</FONT>        }<a name="line.1188"></a>
<FONT color="green">1189</FONT>        catch (Exception e) {<a name="line.1189"></a>
<FONT color="green">1190</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1190"></a>
<FONT color="green">1191</FONT>        }<a name="line.1191"></a>
<FONT color="green">1192</FONT>        finally {<a name="line.1192"></a>
<FONT color="green">1193</FONT>          results.close();<a name="line.1193"></a>
<FONT color="green">1194</FONT>          pStmt.close();<a name="line.1194"></a>
<FONT color="green">1195</FONT>        }<a name="line.1195"></a>
<FONT color="green">1196</FONT>        return id;<a name="line.1196"></a>
<FONT color="green">1197</FONT>      }<a name="line.1197"></a>
<FONT color="green">1198</FONT>      /**<a name="line.1198"></a>
<FONT color="green">1199</FONT>       * Gets a list of all collaborations in the tbl_COLLABORATION table<a name="line.1199"></a>
<FONT color="green">1200</FONT>       * @return an array list of collaboration objects.<a name="line.1200"></a>
<FONT color="green">1201</FONT>       * @throws Exception if error occurs.<a name="line.1201"></a>
<FONT color="green">1202</FONT>       */<a name="line.1202"></a>
<FONT color="green">1203</FONT>      public ArrayList&lt;Collaboration&gt; getCollaborationList() throws Exception {<a name="line.1203"></a>
<FONT color="green">1204</FONT>        ArrayList&lt;Collaboration&gt; list = new ArrayList&lt;Collaboration&gt;();<a name="line.1204"></a>
<FONT color="green">1205</FONT>        Collaboration c;<a name="line.1205"></a>
<FONT color="green">1206</FONT>        ResultSet results = null;<a name="line.1206"></a>
<FONT color="green">1207</FONT>        try {<a name="line.1207"></a>
<FONT color="green">1208</FONT>          String sqlCmd = "SELECT c_id, c_name, c_desc " +<a name="line.1208"></a>
<FONT color="green">1209</FONT>                          "FROM tbl_COLLABORATION";<a name="line.1209"></a>
<FONT color="green">1210</FONT>          stmt = conn.createStatement();<a name="line.1210"></a>
<FONT color="green">1211</FONT>          results = stmt.executeQuery(sqlCmd);<a name="line.1211"></a>
<FONT color="green">1212</FONT>          while (results.next()) {<a name="line.1212"></a>
<FONT color="green">1213</FONT>            c = new Collaboration();<a name="line.1213"></a>
<FONT color="green">1214</FONT>           <a name="line.1214"></a>
<FONT color="green">1215</FONT>            c.setName(results.getString("c_name"));<a name="line.1215"></a>
<FONT color="green">1216</FONT>            c.setDescription(results.getString("c_desc"));<a name="line.1216"></a>
<FONT color="green">1217</FONT>            //TODO getOutcomeType(int collabId)<a name="line.1217"></a>
<FONT color="green">1218</FONT>            //TODO getYear(int collabId)<a name="line.1218"></a>
<FONT color="green">1219</FONT>            //TODO getCollaborationType(int collabId)<a name="line.1219"></a>
<FONT color="green">1220</FONT>            list.add(c);<a name="line.1220"></a>
<FONT color="green">1221</FONT>          }<a name="line.1221"></a>
<FONT color="green">1222</FONT>        }<a name="line.1222"></a>
<FONT color="green">1223</FONT>        catch (Exception e)  {<a name="line.1223"></a>
<FONT color="green">1224</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1224"></a>
<FONT color="green">1225</FONT>          list = null;<a name="line.1225"></a>
<FONT color="green">1226</FONT>        }<a name="line.1226"></a>
<FONT color="green">1227</FONT>        finally {<a name="line.1227"></a>
<FONT color="green">1228</FONT>          results.close();<a name="line.1228"></a>
<FONT color="green">1229</FONT>          stmt.close();<a name="line.1229"></a>
<FONT color="green">1230</FONT>        }<a name="line.1230"></a>
<FONT color="green">1231</FONT>        return list;<a name="line.1231"></a>
<FONT color="green">1232</FONT>        <a name="line.1232"></a>
<FONT color="green">1233</FONT>      }<a name="line.1233"></a>
<FONT color="green">1234</FONT>      /**<a name="line.1234"></a>
<FONT color="green">1235</FONT>       * Creates the tbl_COLLABORATION_TYPE Table.<a name="line.1235"></a>
<FONT color="green">1236</FONT>       */<a name="line.1236"></a>
<FONT color="green">1237</FONT>      public void createCollaborationTypeTable() {<a name="line.1237"></a>
<FONT color="green">1238</FONT>        try {<a name="line.1238"></a>
<FONT color="green">1239</FONT>          String sqlCmd = "CREATE TABLE tbl_COLLABORATION_TYPE (" +<a name="line.1239"></a>
<FONT color="green">1240</FONT>                          "   c_id          INT           NOT NULL" + <a name="line.1240"></a>
<FONT color="green">1241</FONT>                          "                 CONSTRAINT c_id_type_fk REFERENCES tbl_COLLABORATION," +<a name="line.1241"></a>
<FONT color="green">1242</FONT>                          "   c_type        VARCHAR(128)  NOT NULL," +<a name="line.1242"></a>
<FONT color="green">1243</FONT>                          <a name="line.1243"></a>
<FONT color="green">1244</FONT>                          "   CONSTRAINT c_type_pk        PRIMARY KEY(c_id, c_type)" +<a name="line.1244"></a>
<FONT color="green">1245</FONT>                          ")";<a name="line.1245"></a>
<FONT color="green">1246</FONT>          stmt = conn.createStatement();<a name="line.1246"></a>
<FONT color="green">1247</FONT>          stmt.execute(sqlCmd);<a name="line.1247"></a>
<FONT color="green">1248</FONT>          stmt.close();<a name="line.1248"></a>
<FONT color="green">1249</FONT>        }<a name="line.1249"></a>
<FONT color="green">1250</FONT>        catch (Exception e)  {<a name="line.1250"></a>
<FONT color="green">1251</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1251"></a>
<FONT color="green">1252</FONT>        }<a name="line.1252"></a>
<FONT color="green">1253</FONT>      }<a name="line.1253"></a>
<FONT color="green">1254</FONT>      /**<a name="line.1254"></a>
<FONT color="green">1255</FONT>       * Drops the tbl_COLLABORATION_TYPE Table.<a name="line.1255"></a>
<FONT color="green">1256</FONT>       */<a name="line.1256"></a>
<FONT color="green">1257</FONT>      public void dropCollaborationTypeTable() {<a name="line.1257"></a>
<FONT color="green">1258</FONT>        try {<a name="line.1258"></a>
<FONT color="green">1259</FONT>          String sqlCmd = "DROP TABLE tbl_COLLABORATION_TYPE";<a name="line.1259"></a>
<FONT color="green">1260</FONT>          stmt = conn.createStatement();<a name="line.1260"></a>
<FONT color="green">1261</FONT>          stmt.execute(sqlCmd);<a name="line.1261"></a>
<FONT color="green">1262</FONT>          stmt.close();<a name="line.1262"></a>
<FONT color="green">1263</FONT>        }<a name="line.1263"></a>
<FONT color="green">1264</FONT>        catch (Exception e)  {<a name="line.1264"></a>
<FONT color="green">1265</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1265"></a>
<FONT color="green">1266</FONT>        }<a name="line.1266"></a>
<FONT color="green">1267</FONT>      }<a name="line.1267"></a>
<FONT color="green">1268</FONT>      /**<a name="line.1268"></a>
<FONT color="green">1269</FONT>       * Creates the tbl_OUTCOME_TYPE Table.<a name="line.1269"></a>
<FONT color="green">1270</FONT>       */<a name="line.1270"></a>
<FONT color="green">1271</FONT>      public void createOutcomeTypeTable() {<a name="line.1271"></a>
<FONT color="green">1272</FONT>        try {<a name="line.1272"></a>
<FONT color="green">1273</FONT>          String sqlCmd = "CREATE TABLE tbl_OUTCOME_TYPE (" +<a name="line.1273"></a>
<FONT color="green">1274</FONT>                          "   c_id                INT           NOT NULL" + <a name="line.1274"></a>
<FONT color="green">1275</FONT>                          "                                     CONSTRAINT c_id_fk" +<a name="line.1275"></a>
<FONT color="green">1276</FONT>                          "                                     REFERENCES tbl_COLLABORATION," +<a name="line.1276"></a>
<FONT color="green">1277</FONT>                          "   outcome_type        VARCHAR(128)  NOT NULL," +<a name="line.1277"></a>
<FONT color="green">1278</FONT>                          <a name="line.1278"></a>
<FONT color="green">1279</FONT>                          "   CONSTRAINT outcome_pk          PRIMARY KEY(c_id, outcome_type)" +<a name="line.1279"></a>
<FONT color="green">1280</FONT>                          ")";<a name="line.1280"></a>
<FONT color="green">1281</FONT>          stmt = conn.createStatement();<a name="line.1281"></a>
<FONT color="green">1282</FONT>          stmt.execute(sqlCmd);<a name="line.1282"></a>
<FONT color="green">1283</FONT>          stmt.close();<a name="line.1283"></a>
<FONT color="green">1284</FONT>        }<a name="line.1284"></a>
<FONT color="green">1285</FONT>        catch (Exception e)  {<a name="line.1285"></a>
<FONT color="green">1286</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1286"></a>
<FONT color="green">1287</FONT>        }<a name="line.1287"></a>
<FONT color="green">1288</FONT>      }<a name="line.1288"></a>
<FONT color="green">1289</FONT>      /**<a name="line.1289"></a>
<FONT color="green">1290</FONT>       * Drops the tbl_OUTCOME_TYPE Table.<a name="line.1290"></a>
<FONT color="green">1291</FONT>       */<a name="line.1291"></a>
<FONT color="green">1292</FONT>      public void dropOutcomeTypeTable() {<a name="line.1292"></a>
<FONT color="green">1293</FONT>        try {<a name="line.1293"></a>
<FONT color="green">1294</FONT>          String sqlCmd = "DROP TABLE tbl_OUTCOME_TYPE";<a name="line.1294"></a>
<FONT color="green">1295</FONT>          stmt = conn.createStatement();<a name="line.1295"></a>
<FONT color="green">1296</FONT>          stmt.execute(sqlCmd);<a name="line.1296"></a>
<FONT color="green">1297</FONT>          stmt.close();<a name="line.1297"></a>
<FONT color="green">1298</FONT>        }<a name="line.1298"></a>
<FONT color="green">1299</FONT>        catch (Exception e)  {<a name="line.1299"></a>
<FONT color="green">1300</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1300"></a>
<FONT color="green">1301</FONT>        }<a name="line.1301"></a>
<FONT color="green">1302</FONT>      }<a name="line.1302"></a>
<FONT color="green">1303</FONT>      /**<a name="line.1303"></a>
<FONT color="green">1304</FONT>       * Creates the tbl_COLLABORATING_ORGANIZATION Table.<a name="line.1304"></a>
<FONT color="green">1305</FONT>       */<a name="line.1305"></a>
<FONT color="green">1306</FONT>      public void createCollaboratingOrganizationTable() {<a name="line.1306"></a>
<FONT color="green">1307</FONT>        try {<a name="line.1307"></a>
<FONT color="green">1308</FONT>          String sqlCmd = "CREATE TABLE tbl_COLLABORATING_ORGANIZATION (" +<a name="line.1308"></a>
<FONT color="green">1309</FONT>                          "   c_id          INT           NOT NULL" + <a name="line.1309"></a>
<FONT color="green">1310</FONT>                          "                               CONSTRAINT c_id_co_fk" +<a name="line.1310"></a>
<FONT color="green">1311</FONT>                          "                               REFERENCES tbl_COLLABORATION," +<a name="line.1311"></a>
<FONT color="green">1312</FONT>                          "   o_id          INT           NOT NULL" +<a name="line.1312"></a>
<FONT color="green">1313</FONT>                          "                               CONSTRAINT o_id_co_fk" +<a name="line.1313"></a>
<FONT color="green">1314</FONT>                          "                               REFERENCES tbl_ORGANIZATION," +<a name="line.1314"></a>
<FONT color="green">1315</FONT>                          <a name="line.1315"></a>
<FONT color="green">1316</FONT>                          "   CONSTRAINT c_o_id_pk        PRIMARY KEY(c_id, o_id)" +<a name="line.1316"></a>
<FONT color="green">1317</FONT>                          ")";<a name="line.1317"></a>
<FONT color="green">1318</FONT>          stmt = conn.createStatement();<a name="line.1318"></a>
<FONT color="green">1319</FONT>          stmt.execute(sqlCmd);<a name="line.1319"></a>
<FONT color="green">1320</FONT>          stmt.close();<a name="line.1320"></a>
<FONT color="green">1321</FONT>        }<a name="line.1321"></a>
<FONT color="green">1322</FONT>        catch (Exception e)  {<a name="line.1322"></a>
<FONT color="green">1323</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1323"></a>
<FONT color="green">1324</FONT>        }<a name="line.1324"></a>
<FONT color="green">1325</FONT>      }<a name="line.1325"></a>
<FONT color="green">1326</FONT>      /**<a name="line.1326"></a>
<FONT color="green">1327</FONT>       * Drops the tbl_COLLABORATING_ORGANIZATION Table.<a name="line.1327"></a>
<FONT color="green">1328</FONT>       */<a name="line.1328"></a>
<FONT color="green">1329</FONT>      public void dropCollaboratingOrganizationTable() {<a name="line.1329"></a>
<FONT color="green">1330</FONT>        try {<a name="line.1330"></a>
<FONT color="green">1331</FONT>          String sqlCmd = "DROP TABLE tbl_COLLABORATING_ORGANIZATION";<a name="line.1331"></a>
<FONT color="green">1332</FONT>          stmt = conn.createStatement();<a name="line.1332"></a>
<FONT color="green">1333</FONT>          stmt.execute(sqlCmd);<a name="line.1333"></a>
<FONT color="green">1334</FONT>          stmt.close();<a name="line.1334"></a>
<FONT color="green">1335</FONT>        }<a name="line.1335"></a>
<FONT color="green">1336</FONT>        catch (Exception e)  {<a name="line.1336"></a>
<FONT color="green">1337</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1337"></a>
<FONT color="green">1338</FONT>        }<a name="line.1338"></a>
<FONT color="green">1339</FONT>      }<a name="line.1339"></a>
<FONT color="green">1340</FONT>      /**<a name="line.1340"></a>
<FONT color="green">1341</FONT>       * Adds an entry to the tbl_COLLABORATING_ORGANIZATION Table.<a name="line.1341"></a>
<FONT color="green">1342</FONT>       * @param collabId Collaboration Id number.<a name="line.1342"></a>
<FONT color="green">1343</FONT>       * @param orgId Organization Id number.<a name="line.1343"></a>
<FONT color="green">1344</FONT>       */<a name="line.1344"></a>
<FONT color="green">1345</FONT>      public void addCollaboratingOrganization(int collabId, int orgId) {<a name="line.1345"></a>
<FONT color="green">1346</FONT>        try {<a name="line.1346"></a>
<FONT color="green">1347</FONT>          String sqlCmd = "INSERT INTO tbl_COLLABORATING_ORGANIZATION " +<a name="line.1347"></a>
<FONT color="green">1348</FONT>                                "(c_id, o_id) " +<a name="line.1348"></a>
<FONT color="green">1349</FONT>                                "VALUES (?,?)";<a name="line.1349"></a>
<FONT color="green">1350</FONT>          PreparedStatement pStmt = conn.prepareStatement(sqlCmd);<a name="line.1350"></a>
<FONT color="green">1351</FONT>          pStmt.setInt(1, collabId);<a name="line.1351"></a>
<FONT color="green">1352</FONT>          pStmt.setInt(2, orgId);<a name="line.1352"></a>
<FONT color="green">1353</FONT>          pStmt.execute();<a name="line.1353"></a>
<FONT color="green">1354</FONT>          pStmt.close();<a name="line.1354"></a>
<FONT color="green">1355</FONT>        }<a name="line.1355"></a>
<FONT color="green">1356</FONT>        catch (Exception e)  {<a name="line.1356"></a>
<FONT color="green">1357</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1357"></a>
<FONT color="green">1358</FONT>        }<a name="line.1358"></a>
<FONT color="green">1359</FONT>      }<a name="line.1359"></a>
<FONT color="green">1360</FONT>      /**<a name="line.1360"></a>
<FONT color="green">1361</FONT>       * Creates the tbl_COUNTRY table.<a name="line.1361"></a>
<FONT color="green">1362</FONT>       */<a name="line.1362"></a>
<FONT color="green">1363</FONT>      public void createCountryTable() {<a name="line.1363"></a>
<FONT color="green">1364</FONT>        try {<a name="line.1364"></a>
<FONT color="green">1365</FONT>          String sqlCmd = "CREATE TABLE tbl_COUNTRY (" +<a name="line.1365"></a>
<FONT color="green">1366</FONT>                          "   country_id          INT           GENERATED ALWAYS AS IDENTITY" + <a name="line.1366"></a>
<FONT color="green">1367</FONT>                          "                                     CONSTRAINT country_id_pk PRIMARY KEY," +<a name="line.1367"></a>
<FONT color="green">1368</FONT>                          "   country_name        VARCHAR(64)   NOT NULL" +<a name="line.1368"></a>
<FONT color="green">1369</FONT>                          ")";<a name="line.1369"></a>
<FONT color="green">1370</FONT>          stmt = conn.createStatement();<a name="line.1370"></a>
<FONT color="green">1371</FONT>          stmt.execute(sqlCmd);<a name="line.1371"></a>
<FONT color="green">1372</FONT>          stmt.close();<a name="line.1372"></a>
<FONT color="green">1373</FONT>        }<a name="line.1373"></a>
<FONT color="green">1374</FONT>        catch (Exception e) {<a name="line.1374"></a>
<FONT color="green">1375</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1375"></a>
<FONT color="green">1376</FONT>        }<a name="line.1376"></a>
<FONT color="green">1377</FONT>      }<a name="line.1377"></a>
<FONT color="green">1378</FONT>      /**<a name="line.1378"></a>
<FONT color="green">1379</FONT>       * Drops the tbl_COUNTRY Table.<a name="line.1379"></a>
<FONT color="green">1380</FONT>       */<a name="line.1380"></a>
<FONT color="green">1381</FONT>      public void dropCountryTable() {<a name="line.1381"></a>
<FONT color="green">1382</FONT>        try {<a name="line.1382"></a>
<FONT color="green">1383</FONT>          String sqlCmd = "DROP TABLE tbl_COUNTRY";<a name="line.1383"></a>
<FONT color="green">1384</FONT>          stmt = conn.createStatement();<a name="line.1384"></a>
<FONT color="green">1385</FONT>          stmt.execute(sqlCmd);<a name="line.1385"></a>
<FONT color="green">1386</FONT>          stmt.close();<a name="line.1386"></a>
<FONT color="green">1387</FONT>        }<a name="line.1387"></a>
<FONT color="green">1388</FONT>        catch (Exception e)  {<a name="line.1388"></a>
<FONT color="green">1389</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1389"></a>
<FONT color="green">1390</FONT>        }<a name="line.1390"></a>
<FONT color="green">1391</FONT>      }<a name="line.1391"></a>
<FONT color="green">1392</FONT>      <a name="line.1392"></a>
<FONT color="green">1393</FONT>      /**<a name="line.1393"></a>
<FONT color="green">1394</FONT>       * Adds an country to the table "tbl_COUNTRY" in the database.<a name="line.1394"></a>
<FONT color="green">1395</FONT>       * @param name name of country to add.<a name="line.1395"></a>
<FONT color="green">1396</FONT>       */<a name="line.1396"></a>
<FONT color="green">1397</FONT>      public void addCountry(String name) {<a name="line.1397"></a>
<FONT color="green">1398</FONT>        try {<a name="line.1398"></a>
<FONT color="green">1399</FONT>          String sqlCmd = "INSERT INTO tbl_COUNTRY " +  <a name="line.1399"></a>
<FONT color="green">1400</FONT>                          "(country_name)" +<a name="line.1400"></a>
<FONT color="green">1401</FONT>                          "VALUES (?)";<a name="line.1401"></a>
<FONT color="green">1402</FONT>          PreparedStatement pStmt = conn.prepareStatement(sqlCmd);<a name="line.1402"></a>
<FONT color="green">1403</FONT>          pStmt.setString(1, name.trim());<a name="line.1403"></a>
<FONT color="green">1404</FONT>          pStmt.execute();<a name="line.1404"></a>
<FONT color="green">1405</FONT>          pStmt.close();<a name="line.1405"></a>
<FONT color="green">1406</FONT>        }<a name="line.1406"></a>
<FONT color="green">1407</FONT>        catch (Exception e)  {<a name="line.1407"></a>
<FONT color="green">1408</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1408"></a>
<FONT color="green">1409</FONT>        }<a name="line.1409"></a>
<FONT color="green">1410</FONT>      }<a name="line.1410"></a>
<FONT color="green">1411</FONT>      /**<a name="line.1411"></a>
<FONT color="green">1412</FONT>       * Gets the database ID of an country given the name<a name="line.1412"></a>
<FONT color="green">1413</FONT>       * from the "tbl_COUNTRY" table.<a name="line.1413"></a>
<FONT color="green">1414</FONT>       * @param name Country name.<a name="line.1414"></a>
<FONT color="green">1415</FONT>       * @return a number.<a name="line.1415"></a>
<FONT color="green">1416</FONT>       * @throws Exception if error occurs.<a name="line.1416"></a>
<FONT color="green">1417</FONT>       */<a name="line.1417"></a>
<FONT color="green">1418</FONT>      public int getCountryId(String name) throws Exception {<a name="line.1418"></a>
<FONT color="green">1419</FONT>        int id = -1;<a name="line.1419"></a>
<FONT color="green">1420</FONT>        PreparedStatement pStmt = null;<a name="line.1420"></a>
<FONT color="green">1421</FONT>        ResultSet results = null;<a name="line.1421"></a>
<FONT color="green">1422</FONT>        try {<a name="line.1422"></a>
<FONT color="green">1423</FONT>          String sqlCmd = "SELECT country_id " + <a name="line.1423"></a>
<FONT color="green">1424</FONT>                          "FROM tbl_COUNTRY " +  <a name="line.1424"></a>
<FONT color="green">1425</FONT>                          "WHERE country_name = ?";<a name="line.1425"></a>
<FONT color="green">1426</FONT>          pStmt = conn.prepareStatement(sqlCmd);<a name="line.1426"></a>
<FONT color="green">1427</FONT>          pStmt.setString(1, name);<a name="line.1427"></a>
<FONT color="green">1428</FONT>          results = pStmt.executeQuery();<a name="line.1428"></a>
<FONT color="green">1429</FONT>          if (results.next()) {<a name="line.1429"></a>
<FONT color="green">1430</FONT>            id = results.getInt("country_id");<a name="line.1430"></a>
<FONT color="green">1431</FONT>          }<a name="line.1431"></a>
<FONT color="green">1432</FONT>          else {<a name="line.1432"></a>
<FONT color="green">1433</FONT>            id = -1;<a name="line.1433"></a>
<FONT color="green">1434</FONT>          }<a name="line.1434"></a>
<FONT color="green">1435</FONT>        }<a name="line.1435"></a>
<FONT color="green">1436</FONT>        catch (Exception e) {<a name="line.1436"></a>
<FONT color="green">1437</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1437"></a>
<FONT color="green">1438</FONT>        }<a name="line.1438"></a>
<FONT color="green">1439</FONT>        finally {<a name="line.1439"></a>
<FONT color="green">1440</FONT>          results.close();<a name="line.1440"></a>
<FONT color="green">1441</FONT>          pStmt.close();<a name="line.1441"></a>
<FONT color="green">1442</FONT>        }<a name="line.1442"></a>
<FONT color="green">1443</FONT>        return id;<a name="line.1443"></a>
<FONT color="green">1444</FONT>      }<a name="line.1444"></a>
<FONT color="green">1445</FONT>      /**<a name="line.1445"></a>
<FONT color="green">1446</FONT>       * Creates the tbl_YEAR Table.<a name="line.1446"></a>
<FONT color="green">1447</FONT>       */<a name="line.1447"></a>
<FONT color="green">1448</FONT>      public void createYearTable() {<a name="line.1448"></a>
<FONT color="green">1449</FONT>        try {<a name="line.1449"></a>
<FONT color="green">1450</FONT>          String sqlCmd = "CREATE TABLE tbl_YEAR (" +<a name="line.1450"></a>
<FONT color="green">1451</FONT>                          "   c_id          INT       NOT NULL" + <a name="line.1451"></a>
<FONT color="green">1452</FONT>                          "                 CONSTRAINT c_id_year_fk REFERENCES tbl_COLLABORATION," +<a name="line.1452"></a>
<FONT color="green">1453</FONT>                          "   y_type        DATE      NOT NULL," +<a name="line.1453"></a>
<FONT color="green">1454</FONT>                          <a name="line.1454"></a>
<FONT color="green">1455</FONT>                          "   CONSTRAINT y_id_pk      PRIMARY KEY(c_id, y_type)" +<a name="line.1455"></a>
<FONT color="green">1456</FONT>                          ")";<a name="line.1456"></a>
<FONT color="green">1457</FONT>          stmt = conn.createStatement();<a name="line.1457"></a>
<FONT color="green">1458</FONT>          stmt.execute(sqlCmd);<a name="line.1458"></a>
<FONT color="green">1459</FONT>          stmt.close();<a name="line.1459"></a>
<FONT color="green">1460</FONT>        }<a name="line.1460"></a>
<FONT color="green">1461</FONT>        catch (Exception e)  {<a name="line.1461"></a>
<FONT color="green">1462</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1462"></a>
<FONT color="green">1463</FONT>        }<a name="line.1463"></a>
<FONT color="green">1464</FONT>      }<a name="line.1464"></a>
<FONT color="green">1465</FONT>      /**<a name="line.1465"></a>
<FONT color="green">1466</FONT>       * Drops the tbl_YEAR Table.<a name="line.1466"></a>
<FONT color="green">1467</FONT>       */<a name="line.1467"></a>
<FONT color="green">1468</FONT>      public void dropYearTable() {<a name="line.1468"></a>
<FONT color="green">1469</FONT>        try {<a name="line.1469"></a>
<FONT color="green">1470</FONT>          String sqlCmd = "DROP TABLE tbl_YEAR";<a name="line.1470"></a>
<FONT color="green">1471</FONT>          stmt = conn.createStatement();<a name="line.1471"></a>
<FONT color="green">1472</FONT>          stmt.execute(sqlCmd);<a name="line.1472"></a>
<FONT color="green">1473</FONT>          stmt.close();<a name="line.1473"></a>
<FONT color="green">1474</FONT>        }<a name="line.1474"></a>
<FONT color="green">1475</FONT>        catch (Exception e)  {<a name="line.1475"></a>
<FONT color="green">1476</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1476"></a>
<FONT color="green">1477</FONT>        }<a name="line.1477"></a>
<FONT color="green">1478</FONT>      }<a name="line.1478"></a>
<FONT color="green">1479</FONT>      <a name="line.1479"></a>
<FONT color="green">1480</FONT>      <a name="line.1480"></a>
<FONT color="green">1481</FONT>      <a name="line.1481"></a>
<FONT color="green">1482</FONT>      /**<a name="line.1482"></a>
<FONT color="green">1483</FONT>       * Splits a full name into First,Middle,Last names<a name="line.1483"></a>
<FONT color="green">1484</FONT>       * @param name Full name.<a name="line.1484"></a>
<FONT color="green">1485</FONT>       * @return String[] of names.<a name="line.1485"></a>
<FONT color="green">1486</FONT>       */<a name="line.1486"></a>
<FONT color="green">1487</FONT>      public String[] splitUpName(String name) {<a name="line.1487"></a>
<FONT color="green">1488</FONT>        try {<a name="line.1488"></a>
<FONT color="green">1489</FONT>          String[] names = new String[3];<a name="line.1489"></a>
<FONT color="green">1490</FONT>          int firstSpaceIndex = name.indexOf(' ');<a name="line.1490"></a>
<FONT color="green">1491</FONT>          if (firstSpaceIndex == -1) {<a name="line.1491"></a>
<FONT color="green">1492</FONT>            names[0] = name;<a name="line.1492"></a>
<FONT color="green">1493</FONT>            names[1] = "";<a name="line.1493"></a>
<FONT color="green">1494</FONT>            names[2] = "";<a name="line.1494"></a>
<FONT color="green">1495</FONT>          }<a name="line.1495"></a>
<FONT color="green">1496</FONT>          else {<a name="line.1496"></a>
<FONT color="green">1497</FONT>            int lastSpaceIndex = name.lastIndexOf(' ');<a name="line.1497"></a>
<FONT color="green">1498</FONT>            names[0] = name.substring(0, firstSpaceIndex).trim();<a name="line.1498"></a>
<FONT color="green">1499</FONT>            names[1] = name.substring(firstSpaceIndex, lastSpaceIndex).trim();<a name="line.1499"></a>
<FONT color="green">1500</FONT>            names[2] = name.substring(lastSpaceIndex, name.length()).trim();<a name="line.1500"></a>
<FONT color="green">1501</FONT>            <a name="line.1501"></a>
<FONT color="green">1502</FONT>          }<a name="line.1502"></a>
<FONT color="green">1503</FONT>          return names;<a name="line.1503"></a>
<FONT color="green">1504</FONT>        }<a name="line.1504"></a>
<FONT color="green">1505</FONT>        catch (Exception e)  {<a name="line.1505"></a>
<FONT color="green">1506</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1506"></a>
<FONT color="green">1507</FONT>          return null;<a name="line.1507"></a>
<FONT color="green">1508</FONT>        }<a name="line.1508"></a>
<FONT color="green">1509</FONT>      }<a name="line.1509"></a>
<FONT color="green">1510</FONT>      /**<a name="line.1510"></a>
<FONT color="green">1511</FONT>       * Combines a String[] list of names.<a name="line.1511"></a>
<FONT color="green">1512</FONT>       * @param names String[] list.<a name="line.1512"></a>
<FONT color="green">1513</FONT>       * @return Full name.<a name="line.1513"></a>
<FONT color="green">1514</FONT>       */<a name="line.1514"></a>
<FONT color="green">1515</FONT>      public String getFullName(String[] names) {<a name="line.1515"></a>
<FONT color="green">1516</FONT>        try {<a name="line.1516"></a>
<FONT color="green">1517</FONT>          String name = "";<a name="line.1517"></a>
<FONT color="green">1518</FONT>          for (int i = 0; i &lt; names.length; i++) {<a name="line.1518"></a>
<FONT color="green">1519</FONT>            name = names[i] + " ";<a name="line.1519"></a>
<FONT color="green">1520</FONT>          }<a name="line.1520"></a>
<FONT color="green">1521</FONT>          name = name.trim();<a name="line.1521"></a>
<FONT color="green">1522</FONT>          return name;<a name="line.1522"></a>
<FONT color="green">1523</FONT>        }<a name="line.1523"></a>
<FONT color="green">1524</FONT>        catch (Exception e)  {<a name="line.1524"></a>
<FONT color="green">1525</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1525"></a>
<FONT color="green">1526</FONT>          return null;<a name="line.1526"></a>
<FONT color="green">1527</FONT>        }<a name="line.1527"></a>
<FONT color="green">1528</FONT>      }<a name="line.1528"></a>
<FONT color="green">1529</FONT>      /**<a name="line.1529"></a>
<FONT color="green">1530</FONT>       * Overload getFullName, combine set of strings into single name.<a name="line.1530"></a>
<FONT color="green">1531</FONT>       * @param fName First Name.<a name="line.1531"></a>
<FONT color="green">1532</FONT>       * @param mName Middle Name.<a name="line.1532"></a>
<FONT color="green">1533</FONT>       * @param lName Last Name.<a name="line.1533"></a>
<FONT color="green">1534</FONT>       * @return Full name.<a name="line.1534"></a>
<FONT color="green">1535</FONT>       */<a name="line.1535"></a>
<FONT color="green">1536</FONT>      public String getFullName(String fName, String mName, String lName) {<a name="line.1536"></a>
<FONT color="green">1537</FONT>        try {<a name="line.1537"></a>
<FONT color="green">1538</FONT>          String name = "";<a name="line.1538"></a>
<FONT color="green">1539</FONT>          if (fName != " ") {<a name="line.1539"></a>
<FONT color="green">1540</FONT>            name += fName;<a name="line.1540"></a>
<FONT color="green">1541</FONT>          }<a name="line.1541"></a>
<FONT color="green">1542</FONT>          if (mName != " ") {<a name="line.1542"></a>
<FONT color="green">1543</FONT>            name += " " + mName.trim();<a name="line.1543"></a>
<FONT color="green">1544</FONT>          }<a name="line.1544"></a>
<FONT color="green">1545</FONT>          if (lName != " ") {<a name="line.1545"></a>
<FONT color="green">1546</FONT>            name += " " + lName.trim();<a name="line.1546"></a>
<FONT color="green">1547</FONT>          }<a name="line.1547"></a>
<FONT color="green">1548</FONT>          return name;<a name="line.1548"></a>
<FONT color="green">1549</FONT>        }<a name="line.1549"></a>
<FONT color="green">1550</FONT>        catch (Exception e)  {<a name="line.1550"></a>
<FONT color="green">1551</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1551"></a>
<FONT color="green">1552</FONT>          return null;<a name="line.1552"></a>
<FONT color="green">1553</FONT>        }<a name="line.1553"></a>
<FONT color="green">1554</FONT>      }<a name="line.1554"></a>
<FONT color="green">1555</FONT>      /**<a name="line.1555"></a>
<FONT color="green">1556</FONT>       * Gets the number of records in a table.<a name="line.1556"></a>
<FONT color="green">1557</FONT>       * @param tableName Name of table.<a name="line.1557"></a>
<FONT color="green">1558</FONT>       * @return the number of records or -1 if error occurs.<a name="line.1558"></a>
<FONT color="green">1559</FONT>       * @throws SQLException if error occurs.<a name="line.1559"></a>
<FONT color="green">1560</FONT>       */<a name="line.1560"></a>
<FONT color="green">1561</FONT>      public int getTableCount(String tableName) throws SQLException {<a name="line.1561"></a>
<FONT color="green">1562</FONT>        int count = -1;<a name="line.1562"></a>
<FONT color="green">1563</FONT>        ResultSet results = null;<a name="line.1563"></a>
<FONT color="green">1564</FONT>        try {<a name="line.1564"></a>
<FONT color="green">1565</FONT>          String sqlCmd = "SELECT COUNT(*) " +<a name="line.1565"></a>
<FONT color="green">1566</FONT>                          "FROM " + tableName;<a name="line.1566"></a>
<FONT color="green">1567</FONT>          //System.out.println("sqlCmd: " + sqlCmd);<a name="line.1567"></a>
<FONT color="green">1568</FONT>          stmt = conn.createStatement();<a name="line.1568"></a>
<FONT color="green">1569</FONT>          results = stmt.executeQuery(sqlCmd);<a name="line.1569"></a>
<FONT color="green">1570</FONT>          results.next();<a name="line.1570"></a>
<FONT color="green">1571</FONT>          //System.out.print(results.getInt(1));<a name="line.1571"></a>
<FONT color="green">1572</FONT>          count = results.getInt(1);<a name="line.1572"></a>
<FONT color="green">1573</FONT>        }<a name="line.1573"></a>
<FONT color="green">1574</FONT>        catch (Exception e)  {<a name="line.1574"></a>
<FONT color="green">1575</FONT>          logger.log(Level.SEVERE, e.toString());<a name="line.1575"></a>
<FONT color="green">1576</FONT>          return -1;<a name="line.1576"></a>
<FONT color="green">1577</FONT>        }<a name="line.1577"></a>
<FONT color="green">1578</FONT>        finally {<a name="line.1578"></a>
<FONT color="green">1579</FONT>          results.close();<a name="line.1579"></a>
<FONT color="green">1580</FONT>          stmt.close();<a name="line.1580"></a>
<FONT color="green">1581</FONT>        }<a name="line.1581"></a>
<FONT color="green">1582</FONT>        return count;<a name="line.1582"></a>
<FONT color="green">1583</FONT>      }<a name="line.1583"></a>
<FONT color="green">1584</FONT>      <a name="line.1584"></a>
<FONT color="green">1585</FONT>    }<a name="line.1585"></a>
<FONT color="green">1586</FONT>    <a name="line.1586"></a>




























































</PRE>
</BODY>
</HTML>
